﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data.SqlClient;
using System.Data;
using PRET.ReportDataDefine.UIData;

namespace PRET.DBAdapter.DBAccess
{
    public class SQLDataProcess : IDisposable
    {
        private readonly static string JCJName = "JCJ";
        private readonly static string JHJName = "JHJ";
        private static Dictionary<string, string> TeamsHoursDic = new Dictionary<string, string> {
            {"A班", "AHours"},
            {"B班", "BHours"},
            {"C班", "CHours"},
            {"D班", "DHours"},
            {"E班", "EHours"},
            {"F班", "FHours"}
        };
        private readonly static int IntervalMins = 3;
        #region 挤出机SQL
        private readonly static string GET_JCJDETAIL_BY_PRODUCTIONLINE = "SELECT * FROM [pret].[dbo].[JCJDetail] " +
            "WHERE [ProductionLine] = '{0}' AND [RecordTime] >= '{1}' AND [RecordTime] <= '{2}' " +
            "ORDER BY [RecordTime] ASC";
        private readonly static string GET_JCJDETAIL_BY_PRODUCTIONLINE2 = "SELECT * FROM [pret].[dbo].[JCJDetail] " +
            "WHERE [ProductionLine] = '{0}' AND [RecordTime] >= '{1}' AND [RecordTime] <= '{2}'" +
            "ORDER BY [RecordTime] DESC";
        private readonly static string SELECT_TEAMWORKTIME_BY_TIME = "SELECT * FROM [pret].[dbo].[TeamWorkTime]" +
            "WHERE [ProductionLine] = '{0}' AND (([StartTime] <= '{1}' AND [EndTime] >= '{1}') OR ([StartTime] >= '{1}' AND [StartTime] <= '{2}'))" +
            "ORDER BY [StartTime] ASC";
        private readonly static string GET_ALL_PRODUCTIONLINE = "SELECT DISTINCT [ProductionLine]" +
            "FROM (SELECT [ProductionLine] FROM [pret].[dbo].[JCJInfo] " +
            "UNION ALL " +
            "SELECT [ProductionLine] FROM [pret].[dbo].[JHJInfo]) AS a";
        private readonly static string GET_JCJPOWER_BY_TIME = "SELECT [RecordTime],[ElectricPower],[MaxCurrent],[MinCurrent],[MaxPower] FROM [pret].[dbo].[JCJDetail]" +
            "WHERE [ProductionLine] = '{0}' AND [RecordTime] >= '{1}' AND [RecordTime] <= '{2}' AND [ElectricPower] > 0 " +
            "ORDER BY [RecordTime] ASC";
        private readonly static string GET_JCJCURRENT_BY_TIME = "SELECT * FROM [pret].[dbo].[JCJDetail] " +
            "WHERE [ProductionLine] = '{0}' AND [RecordTime] >= '{1}' AND [RecordTime] <= '{2}' AND [CurrCurrent] > 10 AND [JsProduce] = 0";
        #endregion

        #region 均化机SQL
        private readonly static string GET_JHJTRANS_BY_PRODUCTIONLINE = "SELECT * FROM [pret].[dbo].[JHJTransRecordTime] " +
            "WHERE [ProductionLine] = '{0}' AND (([StartTime] >= '{1}' AND [EndTime] <= '{2}') OR ([StartTime] >= '{1}' AND [StartTime] <= '{2}' AND [EndTime] IS NULL)) " +
            "ORDER BY [StartTime] ASC";
        private readonly static string GET_JHJHEATING_BY_PRODUCTIONLINE = "SELECT * FROM [pret].[dbo].[JHJHeatingRecordTime] " +
            "WHERE [ProductionLine] = '{0}' AND (([StartTime] >= '{1}' AND [EndTime] <= '{2}') OR ([StartTime] >= '{1}' AND [StartTime] <= '{2}' AND [EndTime] IS NULL)) " +
            "ORDER BY [StartTime] ASC";
        private readonly static string GET_JHJPACK_BY_PRODUCTIONLINE = "SELECT * FROM [pret].[dbo].[JHJPackRecordTime] " +
            "WHERE [ProductionLine] = '{0}' AND (([StartTime] >= '{1}' AND [EndTime] <= '{2}') OR ([StartTime] >= '{1}' AND [StartTime] <= '{2}' AND [EndTime] IS NULL)) " +
            "ORDER BY [StartTime] ASC";
        private readonly static string GET_JHJPOWER_BY_TIME = "SELECT [RecordTime],[ElectricPower],[MaxCurrent],[MinCurrent],[MaxPower] FROM [pret].[dbo].[JHJDetail]" +
            "WHERE [ProductionLine] = '{0}' AND [RecordTime] >= '{1}' AND [RecordTime] <= '{2}' AND [ElectricPower] > 0 " +
            "ORDER BY [RecordTime] ASC";
        private readonly static string GET_JHJDETAILINFO_BY_TIME = "SELECT * FROM [pret].[dbo].[JHJDetail] " +
            "WHERE [ProductionLine] = '{0}' AND [RecordTime] >= '{1}' AND [RecordTime] <= '{2}' " +
            "ORDER BY [RecordTime] DESC";
        #endregion

        #region 订单时间纠正SQL
        private readonly static string INSERT_IGNOREERPORDER = "INSERT INTO [pret].[dbo].[IgnoreERPOrderTime] " +
           "([ProductionLine],[OrderID]) " +
        "VALUES " +
           "('{0}'" + // <ProductionLine, varchar(10),>
           ",'{1}')";  // <OrderID, varchar(20),>
        private readonly static string GET_ALLIGNOREERPORDER = "SELECT * FROM [pret].[dbo].[IgnoreERPOrderTime]";
        private readonly static string GET_IGNOREERPORDER_BY_ORDERID = "SELECT * FROM [pret].[dbo].[IgnoreERPOrderTime] " +
            "WHERE [OrderID] LIKE '%{0}'";
        private readonly static string DELETE_IGNOREERPORDER_BY_ORDERID = "DELETE FROM [pret].[dbo].[IgnoreERPOrderTime] " +
            "WHERE [OrderID] LIKE '%{0}'";
        private readonly static string DELETE_JCJORDER_BY_TIME = "DELETE FROM [pret].[dbo].[JCJInfo] " +
            "WHERE [ProductionLine] = '{0}' AND [OrderID] LIKE '%{1}' AND [StartTime] = '{2}'";
        private readonly static string DELETE_JHJORDER_BY_TIME = "DELETE FROM [pret].[dbo].[JHJInfo] " +
            "WHERE [ProductionLine] = '{0}' AND [OrderID] LIKE '%{1}' AND [StartTime] = '{2}'";

        private readonly static string GET_JCJORDERLIST_FROM_SYS = "SELECT [ProductionLine],[OrderID],[StartTime],[EndTime],[UpdateTime] FROM [pret].[dbo].[JCJInfo]";
        private readonly static string GET_ORDERLIST_FROM_ERP = "SELECT [ProductionLine],[OrderID],[StartTime],[EndTime] FROM [ERPSystem].[dbo].[ProductionInfo]";
        private readonly static string GET_JHJORDERLIST_FROM_SYS = "SELECT [ProductionLine],[OrderID],[StartTime],[EndTime],[UpdateTime] FROM [pret].[dbo].[JHJInfo]";
        private readonly static string INSERT_JCJINFO = "INSERT INTO [pret].[dbo].[JCJInfo]" +
               "([ProductionLine],[ProLineCode],[OrderID],[StartTime],[EndTime],[UpdateTime])" +
            "VALUES" +
               "('{0}'" + // <ProductionLine, varchar(20),>
               ",'{1}'" + // <ProLineCode, varchar(20),>
               ",'{2}'" + // <OrderID, varchar(20),>
               ",'{3}'" + // <StartTime, datetime,>
               ",'{4}'" + // <EndTime, datetime,>
               ",'{5}')"; // <UpdateTime, datetime>
        private readonly static string INSERT_JHJDATA = "INSERT INTO [pret].[dbo].[JHJInfo]" +
               "([ProductionLine],[ProLineCode],[OrderID]" +
               ",[StartTime],[EndTime],[UpdateTime]) " +
            "VALUES" +
               "('{0}'" + // <ProductionLine, varchar(20),>
               ",'{1}'" + // <ProLineCode, varchar(10),>
               ",'{2}'" + // <OrderID, varchar(20),>
               ",'{3}'" + // <StartTime, datetime,>
               ",'{4}'" + // <EndTime, datetime,>
               ",'{5}')"; // <UpdateTime, datetime>
        #endregion

        #region 生产效率SQL
        private readonly static string GET_ALLPRODUCTIONLINE_FROM_SYS = "SELECT [ProductionLine] FROM [pret].[dbo].[JCJInfo] " +
            "WHERE [UpdateTime] >= '{0}' AND [UpdateTime] <= '{1}' " +
            "GROUP BY [ProductionLine]";
        private readonly static string GET_ALLJCJORDER_BY_PROLINE = "SELECT [OrderID], [StartTime], [UpdateTime] FROM [pret].[dbo].[JCJInfo] " +
            "WHERE [ProductionLine] = '{0}' AND " +
            "(([UpdateTime] >= '{1}' AND [UpdateTime] <= '{2}') OR ([StartTime] >= '{1}' AND [StartTime] <= '{2}') OR ([StartTime] <= '{1}' AND [UpdateTime] >= '{2}')) " +
            "ORDER BY [UpdateTime] ASC";
        private readonly static string GET_OUTPUT_BY_ORDERIDTIME = "SELECT [Bal1RealCount],[Bal2RealCount],[Bal3RealCount] FROM [pret].[dbo].[JCJDetail] " +
            "WHERE [ProductionLine] = '{0}' AND [RecordTime] >= '{1}' AND [RecordTime] <= '{2}' " +
            "ORDER BY [RecordTime] DESC";
        private readonly static string GET_EFFIQUOTA_BY_TYPE = "SELECT * FROM [pret].[dbo].[EfficiencyQuota] WHERE [EffType] = {0} AND [TypeName] = '{1}'";
        private readonly static string UPDATE_EFFIQUOTA = "UPDATE [pret].[dbo].[EfficiencyQuota] " +
            "SET [EffQuota] = {2} " +
            "WHERE [EffType] = {0} AND [TypeName] = '{1}'";
        private readonly static string INSERT_EFFIQUOTA = "INSERT INTO [pret].[dbo].[EfficiencyQuota]" +
            "([TypeName],[EffType],[EffQuota])" +
            "VALUES" +
               "('{0}'" + // <TypeName, varchar(10),>
               ",{1}" + // <EffType, int,>
               ",{2})"; // <EffQuota, float,>
        #endregion

        #region 班组效率SQL
        private readonly static string GET_TEAMTIME = "SELECT * FROM [pret].[dbo].[TeamWorkTime] " +
            "WHERE [TeamID] = '{0}' AND (([StartTime] >= '{1}' AND [StartTime] <= '{2}') OR ([EndTime] >= '{1}' AND [EndTime] <= '{2}'))";
        private readonly static string GET_ALLTEAM_FROM_SYS = "SELECT [TeamID] FROM [pret].[dbo].[TeamWorkTime] " +
            "WHERE [EndTime] >= '{0}' AND [EndTime] <= '{1}' " +
            "GROUP BY [TeamID]";
        private readonly static string GET_PRODUCTIONLINEWORKTIME_BY_TEAMYEAR = "SELECT [ProductionLine],[StartTime],[EndTime] FROM [pret].[dbo].[TeamWorkTime] " +
            "WHERE [TeamID] = '{0}' AND (([StartTime] >= '{1}' AND [StartTime] <= '{2}') OR ([EndTime] >= '{1}' AND [EndTime] <= '{2}')) " +
            "ORDER BY [StartTime] ASC";
        private readonly static string GET_MEMBERID_BY_TIME = "SELECT * FROM [pret].[dbo].[TeamWorkTime] " +
            "WHERE [ProductionLine] = '{0}' AND [StartTime] <= '{1}' AND [EndTime] >= '{1}'";
        private readonly static string GET_TEAMATTENDANCEREST_BY_MONTH = "SELECT * FROM [pret].[dbo].[TeamAttendanceHours] " +
            "WHERE [Month] = '{0}' ORDER BY [UpdateTime] ASC";
        private readonly static string UPDATE_TEAMATTENDANCEREST_BY_MONTH = "UPDATE [pret].[dbo].[TeamAttendanceHours] " +
            "SET [AHours] = {1}, [BHours] = {2}, [CHours] = {3}, [DHours] = {4}, [EHours] = {5}, [FHours] = {6} " +
            "WHERE [Month] = '{0}'";
        private readonly static string INSERT_TEAMATTENDANCEREST = "INSERT INTO [pret].[dbo].[TeamAttendanceHours]" +
            "([Month],[UpdateTime],[AHours],[BHours],[CHours],[DHours],[EHours],[FHours])" +
            "VALUES" +
               "('{0}'" + // <Month, datetime,>
               ",'{1}'" + // <UpdateTime, datetime,>
               ",{2}" + // <AHours, float,>
               ",{3}" + // <BHours, float,>
               ",{4}" + // <CHours, float,>
               ",{5}" + // <DHours, float,>
               ",{6}" + // <EHours, float,>
               ",{7})"; // <FHours, float>
        #endregion

        #region 生产任务SQL
        private readonly static string GET_ALLJCJPRODUCTIONLINE_FROM_SYS = "SELECT ProductionLine, CAST(substring(ProductionLine,2,2) AS int) AS proline FROM " +
            "(SELECT [ProductionLine] FROM [pret].[dbo].[JCJInfo] GROUP BY [ProductionLine]) AS a " +
            "ORDER BY proline, ProductionLine";
        //private readonly static string GET_ALLJHJPRODUCTIONLINE_FROM_SYS = "SELECT [ProductionLine] FROM [pret].[dbo].[JHJInfo ]" +
        //    "GROUP BY [ProductionLine]";
        private readonly static string GET_JCJORDER_BY_UPDATETIME = "SELECT [OrderID], [StartTime], [EndTime], [UpdateTime] FROM [pret].[dbo].[JCJInfo] " +
            "WHERE [ProductionLine] = '{0}'" +
            "ORDER BY [UpdateTime] DESC";
        private readonly static string GET_JCJDETAIL_BY_PROLINE = "SELECT TOP 300 [RecordTime], [Bal1RealCount], [Bal2RealCount], [Bal3RealCount] FROM [pret].[dbo].[JCJDetail] WITH(NOLOCK)" +
            "WHERE [ProductionLine] = '{0}' " +
            "ORDER BY [RecordTime] DESC";
        private readonly static string GET_ALLORDER_FROMJCJ = "SELECT [OrderID] FROM [pret].[dbo].[JCJInfo] " +
            "WHERE [ProductionLine] = '{0}'";
        //private readonly static string GET_JHJORDER_UNFINISHED = "SELECT [OrderID], [StartTime], [Number], [UnitWeight] FROM [pret].[dbo].[JHJInfo] " +
        //    "WHERE [ProductionLine] = '{0}' AND [EndTime] IS NULL";
        //private readonly static string GET_JHJTIME_BY_PROLINE = "SELECT [OrderID], [StartTime], [EndTime], [Number], [UnitWeight] FROM [pret].[dbo].[JHJInfo] " +
        //    "WHERE [ProductionLine] = '{0}' AND [EndTime] IS NOT NULL " +
        //    "ORDER BY [EndTime] DESC";
        private readonly static string GET_WAITORDER_BY_PROLINE = "SELECT [OrderID], [Weight] FROM [pret].[dbo].[WaitingOrder] " +
            "WHERE [ProductionLine] = '{0}'";
        #endregion

        #region 电表SQL
        private readonly static string INSERT_METERDATA = "INSERT INTO [pret].[dbo].[MeterInfo]" +
           "([PeakUnitPrice], [GenUnitPrice], [LowUnitPrice])" +
        "VALUES" +
           "({0}" + // <PeakUnitPrice, float,>
           ",{1}" + // <GenUnitPrice, float,>
           ",{2})"; // <LowUnitPrice, float,>
        private readonly static string UPDATE_METERDATA = "UPDATE [pret].[dbo].[MeterInfo]" +
            "SET [PeakUnitPrice] = {0},[GenUnitPrice] = {1},[LowUnitPrice] = {2}";
        private readonly static string GET_METERDATA = "SELECT * FROM [pret].[dbo].[MeterInfo]";
        #endregion

        #region 高低柜SQL
        private readonly static string GET_HIGHLOWDEVICE_BY_DEVICEID = "SELECT * FROM [pret].[dbo].[HighLowInfo] " +
            "WHERE [DeviceID] = '{0}' AND [Date] >= '{1}' AND [Date] < '{2}'";
        private readonly static string GET_HUMIDITY_TEMPERATURE_BY_TIME = "SELECT * FROM [pret].[dbo].[HumTemInfo] " +
            "WHERE [Date] >= '{0}' AND [Date] < '{1}'";
        private readonly static string GET_HIGHLOWVOLTAGE_BY_DESC = "SELECT * FROM [pret].[dbo].[HighLowVoltage] " +
            "WHERE [DeviceID] = '{0}' AND [UpdateTime] >= '{1}' AND [UpdateTime] < '{2}' " +
            "ORDER BY [UpdateTime] DESC";
        private readonly static string GET_HIGHLOWVOLTAGE_BY_ASC = "SELECT * FROM [pret].[dbo].[HighLowVoltage] " +
            "WHERE [DeviceID] = '{0}' AND [UpdateTime] >= '{1}' AND [UpdateTime] < '{2}' " +
            "ORDER BY [UpdateTime] ASC";
        #endregion

        #region 电视APP SQL
        private readonly static string GET_MESSAGE = "SELECT * FROM [pret].[dbo].[MessageTable]";
        private readonly static string INSERT_MESSAGE = "INSERT INTO [pret].[dbo].[MessageTable] " +
            "([msg1],[FontSize]) VALUES ('{0}',{1})";
        private readonly static string DELETE_MESAGE = "DELETE FROM [pret].[dbo].[MessageTable]";
        private readonly static string GET_SHOWCONFIG = "SELECT * FROM [pret].[dbo].[ShowConfigTable]";
        private readonly static string UPDATE_SHOWCONFIG_WORD = "UPDATE [pret].[dbo].[ShowConfigTable] " +
            "SET [APP_WORD] = {0}";
        private readonly static string INSERT_SHOWCONFIG = "INSERT INTO [pret].[dbo].[ShowConfigTable] " +
            "([APP_WORD],[APP_CTRL1],[APP_CTRL2],[APP_CTRL3]) " +
            "VALUES ({0},{1},{2},{3})";
        private readonly static string GET_JCJPOWER_BY_TIMERANGE = "SELECT * FROM [pret].[dbo].[JCJDetail] " +
            "WHERE [ProductionLine] = '{0}' AND [RecordTime] >= '{1}'  AND [RecordTime] <= '{2}' " +
            "ORDER BY [RecordTime] DESC";
        private readonly static string GET_PROLINEEFF = "SELECT * FROM [pret].[dbo].[ProlineEff] WHERE [Year] = {0}";
        private readonly static string GET_ALL_PRODUCE_TASK = "SELECT * FROM [pret].[dbo].[ProduceTask]";
        #endregion

        #region 生产线待机时间 SQL
        private readonly static string GET_PROLINESTANDBY = "SELECT * FROM [pret].[dbo].[StandbyProLine] WHERE [Year] = {0}";
        #endregion

        #region 滑石母粒机SQL
        private readonly static string GET_HSMLJ_TIME = "SELECT * FROM [pret].[dbo].[HSMLJData] " +
            "WHERE [StartTime] >= '{0}' AND [StartTime] <= '{1}' AND [EndTime] IS NOT NULL " +
            "ORDER BY [StartTime]";
        #endregion

        #region ERP SQL
        private readonly static string GET_ORDERINFO_FROM_ERP = "SELECT * FROM [ERPSystem].[dbo].[ProductionInfo] WHERE [OrderID] LIKE '%{0}'";
        private readonly static string GET_ORDERINFO_FROM_ERP2 = "SELECT * FROM [ERPSystem].[dbo].[ProductionInfo] " +
            "WHERE [OrderID] LIKE '%{0}' AND [ProductionLine] LIKE '{1}-%'";
        private readonly static string GET_ORDERINFO_FROM_ERP3 = "SELECT * FROM [ERPSystem].[dbo].[ProductionInfo] " +
            "WHERE [OrderID] LIKE '%{0}' AND [ProductionLine] LIKE '{1}-%' AND (([StartTime] >= '{2}' AND [StartTime] <= '{3}') OR ([EndTime] >= '{2}' AND [EndTime] <= '{3}') OR ([StartTime] <= '{2}' AND [EndTime] >= '{3}'))";
        private readonly static string GET_EMPLOYEENAME_BY_ID = "SELECT [Name] FROM [ERPSystem].[dbo].[Employe] WHERE [ID] = '{0}'";
        private readonly static string GET_ORDERCOUNT_BY_ENDTIME2 = "SELECT [CurrCount] FROM [ERPSystem].[dbo].[ProductionInfo] " +
            "WHERE [ProductionLine] LIKE '{0}-%' AND [EndTime] >= '{1}' AND [EndTime] <= '{2}'";
        private readonly static string GET_ORDERINFO_BY_PROLINE = "SELECT * FROM [ERPSystem].[dbo].[ProductionInfo] " +
            "WHERE [ProductionLine] LIKE '{0}-%' AND [EndTime] >= '{1}' AND [EndTime] <= '{2}'";
        private readonly static string GET_PROLINESTATUS_BY_PROLINE = "SELECT * FROM [ERPSystem].[dbo].[ProLineStatus] " +
            "WHERE [ProductionLine] LIKE '{0}-%' AND [RecordTime] >= '{1}' AND [RecordTime] < '{2}'";
        private readonly static string GET_WEIGHT_BY_MONTH = "SELECT * FROM [ERPSystem].[dbo].[ProLineStatus] " +
            "WHERE [RecordTime] >= '{0}' AND [RecordTime] < '{1}'";
        #endregion

        //public bool IsConnected
        //{
        //    get
        //    {
        //        if (conn.State == System.Data.ConnectionState.Closed || conn.State == System.Data.ConnectionState.Broken)
        //            return false;
        //        else
        //            return true;
        //    }
        //}

        //private SqlConnection conn = new SqlConnection();
        private string serverAddress = "";
        private string dbName = "";
        private string userid = "";
        private string password = "";
        private string ConnString = "";

        public SQLDataProcess(string serverAddress, string dbName, string userid, string password)
        {
            this.serverAddress = serverAddress;
            this.dbName = dbName;
            this.userid = userid;
            this.password = password;
            this.ConnString = string.Format("Data Source={0};Initial Catalog={1};User Id={2};Password={3};MultipleActiveResultSets=true;", this.serverAddress, this.dbName, this.userid, this.password);
            //if (IsConnected == false)
            //    ConnectSQL();
        }

        public void Dispose()
        {
            //try
            //{
            //    if (conn != null && conn.State != System.Data.ConnectionState.Closed)
            //    {
            //        conn.Close();
            //        conn.Dispose();
            //    }
            //}
            //catch (Exception ex)
            //{
            //    string msg = string.Format("SQLDataProcess.Dispose() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
            //    LogRecord.Error(msg);
            //}
        }

        //public void ConnectSQL()
        //{
        //    try
        //    {
        //        conn.ConnectionString = string.Format("Data Source={0};Initial Catalog={1};User Id={2};Password={3};MultipleActiveResultSets=true;", this.serverAddress, this.dbName, this.userid, this.password);
        //        conn.Open();
        //    }
        //    catch (Exception e)
        //    {
        //        string msg = string.Format("SQLDataProcess.ConnectSQL() Failed: {0}.\r\nDetail: {1}", e.Message, e.StackTrace);
        //        LogRecord.Error(msg);
        //    }
        //}
        private static object lockObj = new object();
        //public void ReConnectSQL()
        //{
        //    lock (lockObj)
        //    {
        //        if (IsConnected == false)
        //            ConnectSQL();
        //    }
        //}

        #region 订单列表相关操作
        /// <summary>
        /// 从DB中获得挤出机订单列表
        /// </summary>
        /// <returns></returns>
        public List<OrderCheckInfo> GetJCJOrderList()
        {
            try
            {
                // 1. 从DB里面获得订单列表
                List<OrderRecordTime> dbOrderList = this.GetOrderListFromSystem(JCJName);
                // 2. 从ERP里面获得时间修正列表
                List<OrderRecordTime> erpOrderList = this.GetOrderListFromERP();
                // 3. 从DB里面获得订单纠正时间的选择列表
                List<string> signList = this.GetOrderTimeSelectSign();
                // 4. 将上面的列表整理返回
                List<OrderCheckInfo> orderList = this.MergerOrderList(dbOrderList, erpOrderList, signList);
                return orderList;
            }
            catch (Exception ex)
            {
                string msg = string.Format("SQLDataProcess.GetJCJOrderList() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                return null;
            }
        }
        /// <summary>
        /// 从DB中获得均化机订单列表
        /// </summary>
        /// <returns></returns>
        public List<OrderCheckInfo> GetJHJOrderList()
        {
            try
            {
                // 1. 从DB里面获得订单列表
                List<OrderRecordTime> dbOrderList = this.GetOrderListFromSystem(JHJName);
                // 2. 从ERP里面获得时间修正列表
                List<OrderRecordTime> erpOrderList = this.GetOrderListFromERP();
                // 3. 从DB里面获得订单纠正时间的选择列表
                List<string> signList = this.GetOrderTimeSelectSign();
                // 4. 将上面的列表整理返回
                List<OrderCheckInfo> orderList = this.MergerOrderList(dbOrderList, erpOrderList, signList);
                // TODO: 5. 由于WINCC里面记录的OrderID和ERP里面提供的稍有出入，所以读取ERP里的OrderID名称.
                return orderList;
            }
            catch (Exception ex)
            {
                string msg = string.Format("SQLDataProcess.GetJHJOrderList() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                return null;
            }
        }
        private List<OrderRecordTime> GetOrderListFromSystem(string deviceCode)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                ////ReConnectSQL();
                List<OrderRecordTime> retList = new List<OrderRecordTime>();
                string query = "";
                if (deviceCode == JCJName)
                    query = GET_JCJORDERLIST_FROM_SYS;
                else if (deviceCode == JHJName)
                    query = GET_JHJORDERLIST_FROM_SYS;
                else
                    return retList;

                // <OrderID, <ProductionLine, OrderCheckDetail>>
                Dictionary<string, Dictionary<string, List<OrderCheckDetail>>> recordList = new Dictionary<string, Dictionary<string, List<OrderCheckDetail>>>();
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        string OrderID = reader["OrderID"].ToString();
                        if (string.IsNullOrEmpty(OrderID) || OrderID == "MO0000000000")
                            continue;
                        OrderCheckDetail order = new OrderCheckDetail();
                        string proLine = reader["ProductionLine"].ToString();
                        order.ProductionLine = proLine;
                        order.ProductionLine = reader["ProductionLine"].ToString();
                        if (!string.IsNullOrEmpty(reader["StartTime"].ToString()))
                            order.StartTime = DateTime.Parse(reader["StartTime"].ToString());
                        if (!string.IsNullOrEmpty(reader["EndTime"].ToString()))
                            order.EndTime = DateTime.Parse(reader["EndTime"].ToString());
                        order.UpdateTime = DateTime.Parse(reader["UpdateTime"].ToString());
                        if (recordList.Keys.Contains(OrderID))
                        {
                            if (recordList[OrderID].Keys.Contains(proLine))
                                recordList[OrderID][proLine].Add(order);
                            else
                                recordList[OrderID].Add(proLine, new List<OrderCheckDetail> { order });
                        }
                        else
                        {
                            Dictionary<string, List<OrderCheckDetail>> dic = new Dictionary<string, List<OrderCheckDetail>>();
                            dic.Add(proLine, new List<OrderCheckDetail> { order });
                            recordList.Add(OrderID, dic);
                        }
                    }
                    reader.Close();
                }
                foreach (KeyValuePair<string, Dictionary<string, List<OrderCheckDetail>>> item in recordList)
                {
                    foreach (KeyValuePair<string, List<OrderCheckDetail>> item2 in item.Value)
                    {
                        OrderRecordTime obj = new OrderRecordTime();
                        obj.OrderString = item.Key;
                        obj.ProductionLine = item2.Key;
                        obj.RecordList = item2.Value;
                        ERPProductionInfo info = GetProductionInfo(item.Key, item2.Key);
                        if (info != null)
                            obj.OrderID = info.OrderID;
                        else
                            obj.OrderID = item.Key;
                        retList.Add(obj);
                    }
                }
                return retList;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetOrderListFromSystem() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
                return null;
            }
        }
        private List<OrderRecordTime> GetOrderListFromERP()
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                Dictionary<string, Dictionary<string, List<OrderCheckDetail>>> recordList = new Dictionary<string, Dictionary<string, List<OrderCheckDetail>>>();
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(GET_ORDERLIST_FROM_ERP, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        OrderCheckDetail obj = new OrderCheckDetail();
                        string[] split = reader["ProductionLine"].ToString().Split(new char[] { '-' }, 2);
                        if (split.Length > 0)
                            obj.ProductionLine = split[0];
                        else
                            continue;
                        string proLine = obj.ProductionLine;
                        string OrderID = reader["OrderID"].ToString();
                        if (!string.IsNullOrEmpty(reader["StartTime"].ToString()))
                        {
                            obj.StartTime = DateTime.Parse(reader["StartTime"].ToString());
                        }
                        if (!string.IsNullOrEmpty(reader["EndTime"].ToString()))
                        {
                            obj.EndTime = DateTime.Parse(reader["EndTime"].ToString());
                            obj.UpdateTime = obj.EndTime;
                        }
                        if (obj.StartTime == null || obj.EndTime == null)
                            continue;
                        if (recordList.Keys.Contains(OrderID))
                        {
                            if (recordList[OrderID].Keys.Contains(proLine))
                                recordList[OrderID][proLine].Add(obj);
                            else
                                recordList[OrderID].Add(proLine, new List<OrderCheckDetail> { obj });
                        }
                        else
                        {
                            Dictionary<string, List<OrderCheckDetail>> dic = new Dictionary<string, List<OrderCheckDetail>>();
                            dic.Add(proLine, new List<OrderCheckDetail> { obj });
                            recordList.Add(OrderID, dic);
                        }
                    }
                    reader.Close();
                }
                List<OrderRecordTime> retList = new List<OrderRecordTime>();
                foreach (KeyValuePair<string, Dictionary<string, List<OrderCheckDetail>>> item in recordList)
                {
                    foreach (KeyValuePair<string, List<OrderCheckDetail>> item2 in item.Value)
                    {
                        OrderRecordTime record = new OrderRecordTime();
                        record.OrderID = item.Key;
                        record.ProductionLine = item2.Key;
                        record.RecordList = item2.Value;
                        retList.Add(record);
                    }
                }
                return retList;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetOrderListFromERP() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                //////ReConnectSQL();
                return null;
            }
        }
        private List<string> GetOrderTimeSelectSign()
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                List<string> retList = new List<string>();
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(GET_ALLIGNOREERPORDER, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        string order = reader["OrderID"].ToString();
                        retList.Add(order);
                    }
                    reader.Close();
                }
                return retList;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetOrderListFromERP() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
                return null;
            }
        }
        private List<OrderCheckInfo> MergerOrderList(List<OrderRecordTime> dbList, List<OrderRecordTime> erpList, List<string> signList)
        {
            List<OrderCheckInfo> retList = new List<OrderCheckInfo>();
            // 先把erp列表中需要过滤的订单标注出来
            if (erpList != null)
            {
                foreach (OrderRecordTime erpItem in erpList)
                {
                    OrderCheckInfo orderObj = new OrderCheckInfo();
                    orderObj.ProductionLine = erpItem.ProductionLine;
                    orderObj.OrderID = erpItem.OrderID;
                    orderObj.OrderString = erpItem.OrderString;
                    orderObj.SystemRecord = null;
                    orderObj.ERPRecord = erpItem.RecordList;
                    orderObj.SelectSign = 1;
                    if (signList != null && signList.Count > 0)
                    {
                        foreach (string order in signList)
                        {
                            if (order == erpItem.OrderID)
                            {
                                orderObj.IsIgnore = 1;
                                break;
                            }
                        }
                    }
                    retList.Add(orderObj);
                }
            }
            // 通过遍历将系统记录的订单号和ERP中的做匹配
            if (dbList != null && dbList.Count > 0)
            {
                for (int i = 0; i < retList.Count; i++)
                {
                    for (int j = 0; j < dbList.Count; j++)
                    {
                        if (retList[i].OrderID == dbList[j].OrderID && retList[i].ProductionLine == dbList[j].ProductionLine)
                        {
                            retList[i].OrderString = dbList[j].OrderString;
                            retList[i].SystemRecord = dbList[j].RecordList;
                            dbList.RemoveAt(j);
                        }
                    }
                }
            }
            // 如果还有未完成的订单，把这些订单也加入列表
            if (dbList != null && dbList.Count > 0)
            {
                foreach (OrderRecordTime dbTime in dbList)
                {
                    OrderCheckInfo orderObj = new OrderCheckInfo();
                    orderObj.ProductionLine = dbTime.ProductionLine;
                    orderObj.OrderID = dbTime.OrderID;
                    orderObj.OrderString = dbTime.OrderString;
                    orderObj.SystemRecord = dbTime.RecordList;
                    orderObj.ERPRecord = null;
                    orderObj.SelectSign = 1;
                    orderObj.IsIgnore = 0;
                    retList.Add(orderObj);
                }
            }
            retList.Sort((left, right) =>
            {
                return string.Compare(left.OrderID, right.OrderID);
            });
            return retList;
        }

        public void CorrectJCJOrderTime(OrderCheckInfo order)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            SqlCommand cmd = null;
            try
            {
                if (order.ERPRecord == null || order.ERPRecord.Count == 0)
                    return;

                //ReConnectSQL();
                // 1. 修改JCJ Summary表格
                UpdateJCJSummaryTime(order);
                // 2. 从IgnoreERPOrderTime表格中删除特定的订单
                string delete = string.Format(DELETE_IGNOREERPORDER_BY_ORDERID, order.OrderID);
                using (cmd = new SqlCommand(delete, conn))
                {
                    cmd.CommandTimeout = 0;
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.CorrectJCJOrderTime() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
            }
            finally
            {
                conn.Close();
            }
        }
        private void UpdateJCJSummaryTime(OrderCheckInfo order)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                // 1. 删除老的系统记录时间
                if (order.SystemRecord == null || order.SystemRecord.Count == 0)
                {
                    foreach (OrderCheckDetail item in order.SystemRecord)
                    {
                        string delete = string.Format(DELETE_JCJORDER_BY_TIME, order.ProductionLine, order.OrderString, item.StartTime);
                        SqlConnection conn = new SqlConnection();
                        conn.ConnectionString = this.ConnString;
                        conn.Open();
                        using (cmd = new SqlCommand(delete, conn))
                        {
                            cmd.CommandTimeout = 0;
                            cmd.ExecuteNonQuery();
                        }
                        conn.Close();
                    }
                }
                // 2. 将ERP时间更新到系统数据库中
                foreach (OrderCheckDetail item in order.ERPRecord)
                {
                    // 确保数据库中没有相同的记录
                    string query = string.Format(DELETE_JCJORDER_BY_TIME, order.ProductionLine, order.OrderString, (DateTime)item.StartTime);
                    SqlConnection conn = new SqlConnection();
                    conn.ConnectionString = this.ConnString;
                    conn.Open();
                    using (cmd = new SqlCommand(query, conn))
                    {
                        cmd.CommandTimeout = 0;
                        cmd.ExecuteNonQuery(); 
                    }
                    string proline = "JC" + order.ProductionLine.Substring(1);
                    query = string.Format(INSERT_JCJINFO, order.ProductionLine, proline, order.OrderString, item.StartTime, item.EndTime, item.UpdateTime);
                    using (cmd = new SqlCommand(query, conn))
                    { 
                        cmd.CommandTimeout = 0;
                        cmd.ExecuteNonQuery(); 
                    }
                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.UpdateJCJSummaryTime() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
            }
        }

        public void CorrectJHJOrderTime(OrderCheckInfo order)
        {
            SqlCommand cmd = null;
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            try
            {
                if (order.ERPRecord == null || order.ERPRecord.Count == 0)
                    return;
                //ReConnectSQL();
                // 1. 修改JCJ Summary表格
                UpdateJHJSummaryTime(order);
                // 2. 从IgnoreERPOrderTime表格中删除特定的订单
                string delete = string.Format(DELETE_IGNOREERPORDER_BY_ORDERID, order.OrderID);
                using (cmd = new SqlCommand(delete, conn))
                { cmd.ExecuteNonQuery(); }
            }
            catch (Exception ex)
            {
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.CorrectJHJOrderTime() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
            }
            finally
            {
                conn.Close();
            }
        }
        private void UpdateJHJSummaryTime(OrderCheckInfo order)
        {
            SqlCommand cmd = null;
            try
            {
                //ReConnectSQL();
                // 1. 删除老的系统记录时间
                if (order.SystemRecord == null || order.SystemRecord.Count == 0)
                {
                    foreach (OrderCheckDetail item in order.SystemRecord)
                    {
                        string delete = string.Format(DELETE_JHJORDER_BY_TIME, order.ProductionLine, order.OrderString, item.StartTime);
                        SqlConnection conn = new SqlConnection();
                        conn.ConnectionString = this.ConnString;
                        conn.Open();
                        using (cmd = new SqlCommand(delete, conn))
                        {
                            cmd.CommandTimeout = 0;
                            cmd.ExecuteNonQuery();
                        }
                    }
                }
                // 2. 将ERP时间更新到系统数据库中
                foreach (OrderCheckDetail item in order.ERPRecord)
                {
                    // 确保数据库中没有相同的记录
                    string query = string.Format(DELETE_JHJORDER_BY_TIME, order.ProductionLine, order.OrderString, (DateTime)item.StartTime);
                    SqlConnection conn = new SqlConnection();
                    conn.ConnectionString = this.ConnString;
                    conn.Open();
                    using (cmd = new SqlCommand(query, conn))
                    {
                        cmd.CommandTimeout = 0;
                        cmd.ExecuteNonQuery();
                    }
                    string proline = "JH" + order.ProductionLine.Substring(1);
                    query = string.Format(INSERT_JHJDATA, order.ProductionLine, proline, order.OrderString, item.StartTime, item.EndTime, item.UpdateTime);
                    using (cmd = new SqlCommand(query, conn))
                    {
                        cmd.CommandTimeout = 0;
                        cmd.ExecuteNonQuery();
                    }
                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.UpdateJHJSummaryTime() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
            }
        }

        public void IgnoreERPOrderTime(OrderCheckInfo order)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                bool isExist = false;
                string query = string.Format(GET_IGNOREERPORDER_BY_ORDERID, order.OrderID);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    if (reader.Read())
                        isExist = true;
                    reader.Close();
                }
                conn.Close();
                if (!isExist)
                {
                    conn = new SqlConnection();
                    conn.ConnectionString = this.ConnString;
                    conn.Open();
                    query = string.Format(INSERT_IGNOREERPORDER, order.ProductionLine, order.OrderID);
                    using (cmd = new SqlCommand(query, conn))
                    {
                        cmd.CommandTimeout = 0;
                        cmd.ExecuteNonQuery();
                    }
                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.IgnoreERPOrderTime() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
            }
        }
        #endregion

        /// <summary>
        /// 从ERP写入的DB里面获得特定订单号的产品信息
        /// </summary>
        /// <param name="orderID"></param>
        /// <returns></returns>
        public ERPProductionInfo GetProductionInfo(string orderID, string productionline = "")
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            try
            {
                //ReConnectSQL();
                string query = "";
                ERPProductionInfo info = null;
                if (!string.IsNullOrEmpty(productionline))
                {
                    query = string.Format(GET_ORDERINFO_FROM_ERP2, orderID, productionline);
                    conn.Open();
                    using (cmd = new SqlCommand(query, conn))
                    {
                        cmd.CommandTimeout = 0;
                        reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                        if (reader.Read())
                        {
                            info = new ERPProductionInfo();
                            info.OrderID = reader["OrderID"].ToString();
                            info.ProductName = reader["ProductName"].ToString();
                            info.BatchID = reader["BatchID"].ToString();
                            info.StockID = reader["StockID"].ToString();
                            info.Count = double.Parse(reader["Count"].ToString());
                            info.CurrCount = double.Parse(reader["CurrCount"].ToString());
                        }
                        reader.Close();
                    }
                }
                if (info != null)
                    return info;

                conn.Open();
                query = string.Format(GET_ORDERINFO_FROM_ERP, orderID);
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    if (reader.Read())
                    {
                        info = new ERPProductionInfo();
                        info.OrderID = reader["OrderID"].ToString();
                        info.ProductName = reader["ProductName"].ToString();
                        info.BatchID = reader["BatchID"].ToString();
                        info.StockID = reader["StockID"].ToString();
                        info.Count = double.Parse(reader["Count"].ToString());
                        info.CurrCount = double.Parse(reader["CurrCount"].ToString());
                    }
                    reader.Close();
                }
                return info;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetProductionInfo() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
                return null;
            }
        }

        public string GetEmployeeName(string employeeID)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                string employeeName = employeeID;
                string query = string.Format(GET_EMPLOYEENAME_BY_ID, employeeID);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    if (reader.Read())
                    {
                        employeeName = reader["Name"].ToString();
                    }
                    reader.Close();
                }
                return employeeName;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetEmployeeName() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
                return employeeID;
            }
        }

        #region 获得挤出机报表数据
        public List<ProductionReport> GetProductionReport(OrderCheckInfo order)
        {
            try
            {
                // 1. 保存用户选择的订单时间
                //SaveOrderTimeSelection(JCJName, order);
                List<ProductionReport> retList = new List<ProductionReport>();
                // 2. 获得挤出机Summary数据
                List<ProductionReportSummary> sumList = GetProductionReportSummary(order);
                if (sumList.Count == 0)
                    return retList;
                // 3. 获得挤出机详细数据
                for (int i = 0; i < sumList.Count; i++)
                {
                    ProductionReport obj = new ProductionReport();
                    ProductionReportSummary summary = sumList[i];
                    obj.DataList = GetProductionReportDetail(ref summary);
                    obj.Summary = summary;
                    retList.Add(obj);
                }
                return retList;
            }
            catch (Exception ex)
            {
                string msg = string.Format("SQLDataProcess.GetProductionReport() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                return null;
            }
        }
        private List<ProductionReportSummary> GetProductionReportSummary(OrderCheckInfo order)
        {
            try
            {
                List<ProductionReportSummary> retList = new List<ProductionReportSummary>();
                List<OrderCheckDetail> timeList = new List<OrderCheckDetail>();
                if (order.SelectSign == 1)
                    timeList = order.SystemRecord;
                else
                    timeList = order.ERPRecord;
                // 从DB中获得指定order的信息
                foreach (OrderCheckDetail item in timeList)
                {
                    ProductionReportSummary summary = new ProductionReportSummary();
                    summary.ProductionLine = item.ProductionLine;
                    summary.ProLineCode = "";
                    summary.OrderID = order.OrderID;
                    summary.StartTime = item.StartTime;
                    summary.EndTime = item.EndTime;
                    summary.UpdateTime = item.UpdateTime;
                    // 从ERP中获得指定信息
                    ERPProductionInfo erpInfo = GetProductionInfo(summary.OrderID, item.ProductionLine);
                    if (erpInfo != null)
                    {
                        summary.OrderID = erpInfo.OrderID;
                        summary.ProductName = erpInfo.ProductName;
                        summary.BatchID = erpInfo.BatchID;
                        summary.Count = erpInfo.Count;
                        summary.CurrCount = erpInfo.CurrCount;
                    }
                    retList.Add(summary);
                }
                return retList;
            }
            catch (Exception ex)
            {
                string msg = string.Format("SQLDataProcess.GetProductionReportSummary() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                return null;
            }
        }
        private List<ProductionReportDetailInfo> GetProductionReportDetail(ref ProductionReportSummary summary)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                if (summary.StartTime == null || summary.UpdateTime == null)
                    return null;
                List<ProductionReportDetailInfo> retList = new List<ProductionReportDetailInfo>();
                DateTime startTime = (DateTime)summary.StartTime;
                DateTime endTime = (DateTime)summary.UpdateTime;
                bool isBegin = true, isSign = false;
                bool unFinished = false;
                double beginB1 = 0;
                double beginB2 = 0;
                double beginB3 = 0;
                double beginP = 0;
                double endP = 0;
                ProductionReportDetailInfo detail = null;
                DateTime nextTime = startTime.AddMinutes(30);
                // 从DB中获得指定order的信息
                string query = string.Format(GET_JCJDETAIL_BY_PRODUCTIONLINE, summary.ProductionLine, startTime, endTime);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        // 去读订单summary相关数据
                        if (isBegin)
                        {
                            beginP = double.Parse(reader["ElectricPower"].ToString()) / 10;
                            beginB1 = double.Parse(reader["Bal1RealCount"].ToString());
                            beginB2 = double.Parse(reader["Bal2RealCount"].ToString());
                            beginB3 = double.Parse(reader["Bal3RealCount"].ToString());
                            summary.Bal1Scale = double.Parse(reader["Bal1Scale"].ToString());
                            summary.Bal2Scale = double.Parse(reader["Bal2Scale"].ToString());
                            summary.Bal3Scale = double.Parse(reader["Bal3Scale"].ToString());
                            summary.JCJ1TempOffset = double.Parse(reader["JCJ1TempOffset"].ToString());
                            summary.JCJ2TempOffset = double.Parse(reader["JCJ2TempOffset"].ToString());
                            summary.JCJ3TempOffset = double.Parse(reader["JCJ3TempOffset"].ToString());
                            summary.JCJ4TempOffset = double.Parse(reader["JCJ4TempOffset"].ToString());
                            summary.JCJ5TempOffset = double.Parse(reader["JCJ5TempOffset"].ToString());
                            summary.JCJ6TempOffset = double.Parse(reader["JCJ6TempOffset"].ToString());
                            summary.JCJ7TempOffset = double.Parse(reader["JCJ7TempOffset"].ToString());
                            summary.JCJ8TempOffset = double.Parse(reader["JCJ8TempOffset"].ToString());
                            summary.JCJ9TempOffset = double.Parse(reader["JCJ9TempOffset"].ToString());
                            summary.JCJ10TempOffset = double.Parse(reader["JCJ10TempOffset"].ToString());
                            summary.JCJ1SetTemp = double.Parse(reader["JCJ1SetTemp"].ToString());
                            summary.JCJ2SetTemp = double.Parse(reader["JCJ2SetTemp"].ToString());
                            summary.JCJ3SetTemp = double.Parse(reader["JCJ3SetTemp"].ToString());
                            summary.JCJ4SetTemp = double.Parse(reader["JCJ4SetTemp"].ToString());
                            summary.JCJ5SetTemp = double.Parse(reader["JCJ5SetTemp"].ToString());
                            summary.JCJ6SetTemp = double.Parse(reader["JCJ6SetTemp"].ToString());
                            summary.JCJ7SetTemp = double.Parse(reader["JCJ7SetTemp"].ToString());
                            summary.JCJ8SetTemp = double.Parse(reader["JCJ8SetTemp"].ToString());
                            summary.JCJ9SetTemp = double.Parse(reader["JCJ9SetTemp"].ToString());
                            summary.JCJ10SetTemp = double.Parse(reader["JCJ10SetTemp"].ToString());
                            summary.ExtruderMaxRevolvingSpeed = double.Parse(reader["ExtruderMaxRevolvingSpeed"].ToString());
                            summary.ExtruderMinRevolvingSpeed = double.Parse(reader["ExtruderMinRevolvingSpeed"].ToString());
                            summary.ExtruderMaxCurrent = double.Parse(reader["ExtruderMaxCurrent"].ToString());
                            summary.ExtruderMinCurrent = double.Parse(reader["ExtruderMinCurrent"].ToString());
                            summary.MaxRongZhi = double.Parse(reader["MaxRongZhi"].ToString()) / 10;
                            summary.MinRongZhi = double.Parse(reader["MinRongZhi"].ToString()) / 10;
                            summary.MaxRemnant = double.Parse(reader["MaxRemnant"].ToString()) / 10;
                            summary.MinRemnant = double.Parse(reader["MinRemnant"].ToString()) / 10;
                            summary.ProLineCode = reader["ProLineCode"].ToString();
                            isBegin = false;
                        }
                        double Bal1RealCount = double.Parse(reader["Bal1RealCount"].ToString());
                        double Bal2RealCount = double.Parse(reader["Bal2RealCount"].ToString());
                        double Bal3RealCount = double.Parse(reader["Bal3RealCount"].ToString());
                        if (Bal1RealCount == 0 && Bal2RealCount == 0 && Bal3RealCount == 0)
                            continue;
                        detail = new ProductionReportDetailInfo();
                        detail.RecordTime = DateTime.Parse(reader["RecordTime"].ToString());
                        detail.Bal1RealCount = double.Parse(reader["Bal1RealCount"].ToString());
                        detail.Bal2RealCount = double.Parse(reader["Bal2RealCount"].ToString());
                        detail.Bal3RealCount = double.Parse(reader["Bal3RealCount"].ToString());
                        detail.WLJRevolvingSpeed = double.Parse(reader["WLJRevolvingSpeed"].ToString());
                        if (isSign == false)
                        {
                            summary.JCJ1TempOffset = double.Parse(reader["JCJ1TempOffset"].ToString());
                            summary.JCJ2TempOffset = double.Parse(reader["JCJ2TempOffset"].ToString());
                            summary.JCJ3TempOffset = double.Parse(reader["JCJ3TempOffset"].ToString());
                            summary.JCJ4TempOffset = double.Parse(reader["JCJ4TempOffset"].ToString());
                            summary.JCJ5TempOffset = double.Parse(reader["JCJ5TempOffset"].ToString());
                            summary.JCJ6TempOffset = double.Parse(reader["JCJ6TempOffset"].ToString());
                            summary.JCJ7TempOffset = double.Parse(reader["JCJ7TempOffset"].ToString());
                            summary.JCJ8TempOffset = double.Parse(reader["JCJ8TempOffset"].ToString());
                            summary.JCJ9TempOffset = double.Parse(reader["JCJ9TempOffset"].ToString());
                            summary.JCJ10TempOffset = double.Parse(reader["JCJ10TempOffset"].ToString());
                            summary.JCJ1SetTemp = double.Parse(reader["JCJ1SetTemp"].ToString());
                            summary.JCJ2SetTemp = double.Parse(reader["JCJ2SetTemp"].ToString());
                            summary.JCJ3SetTemp = double.Parse(reader["JCJ3SetTemp"].ToString());
                            summary.JCJ4SetTemp = double.Parse(reader["JCJ4SetTemp"].ToString());
                            summary.JCJ5SetTemp = double.Parse(reader["JCJ5SetTemp"].ToString());
                            summary.JCJ6SetTemp = double.Parse(reader["JCJ6SetTemp"].ToString());
                            summary.JCJ7SetTemp = double.Parse(reader["JCJ7SetTemp"].ToString());
                            summary.JCJ8SetTemp = double.Parse(reader["JCJ8SetTemp"].ToString());
                            summary.JCJ9SetTemp = double.Parse(reader["JCJ9SetTemp"].ToString());
                            summary.JCJ10SetTemp = double.Parse(reader["JCJ10SetTemp"].ToString());
                            if (summary.JCJ1TempOffset != 0 && summary.JCJ2TempOffset != 0 && summary.JCJ3TempOffset != 0 && summary.JCJ4TempOffset != 0 && summary.JCJ5TempOffset != 0 &&
                                summary.JCJ6TempOffset != 0 && summary.JCJ7TempOffset != 0 && summary.JCJ8TempOffset != 0 && summary.JCJ9TempOffset != 0 && summary.JCJ10TempOffset != 0 &&
                                summary.JCJ1SetTemp != 0 && summary.JCJ2SetTemp != 0 && summary.JCJ3SetTemp != 0 && summary.JCJ4SetTemp != 0 && summary.JCJ5SetTemp != 0 &&
                                summary.JCJ6SetTemp != 0 && summary.JCJ7SetTemp != 0 && summary.JCJ8SetTemp != 0 && summary.JCJ9SetTemp != 0 && summary.JCJ10SetTemp != 0)
                                isSign = true;
                            summary.ExtruderMaxRevolvingSpeed = double.Parse(reader["ExtruderMaxRevolvingSpeed"].ToString());
                            summary.ExtruderMinRevolvingSpeed = double.Parse(reader["ExtruderMinRevolvingSpeed"].ToString());
                            summary.ExtruderMaxCurrent = double.Parse(reader["ExtruderMaxCurrent"].ToString());
                            summary.ExtruderMinCurrent = double.Parse(reader["ExtruderMinCurrent"].ToString());
                            summary.MaxRongZhi = double.Parse(reader["MaxRongZhi"].ToString()) / 10;
                            summary.MinRongZhi = double.Parse(reader["MinRongZhi"].ToString()) / 10;
                            summary.MaxRemnant = double.Parse(reader["MaxRemnant"].ToString()) / 10;
                            summary.MinRemnant = double.Parse(reader["MinRemnant"].ToString()) / 10;
                        }
                        detail.JCJ1RealTemp = double.Parse(reader["JCJ1RealTemp"].ToString());
                        detail.JCJ2RealTemp = double.Parse(reader["JCJ2RealTemp"].ToString());
                        detail.JCJ3RealTemp = double.Parse(reader["JCJ3RealTemp"].ToString());
                        detail.JCJ4RealTemp = double.Parse(reader["JCJ4RealTemp"].ToString());
                        detail.JCJ5RealTemp = double.Parse(reader["JCJ5RealTemp"].ToString());
                        detail.JCJ6RealTemp = double.Parse(reader["JCJ6RealTemp"].ToString());
                        detail.JCJ7RealTemp = double.Parse(reader["JCJ7RealTemp"].ToString());
                        detail.JCJ8RealTemp = double.Parse(reader["JCJ8RealTemp"].ToString());
                        detail.JCJ9RealTemp = double.Parse(reader["JCJ9RealTemp"].ToString());
                        detail.JCJ10RealTemp = double.Parse(reader["JCJ10RealTemp"].ToString());
                        detail.ExtruderRevolvingRealSpeed = double.Parse(reader["ExtruderRevolvingRealSpeed"].ToString());
                        detail.ExtruderRealCurrent = double.Parse(reader["ExtruderRealCurrent"].ToString());
                        detail.RealRongZhi = double.Parse(reader["RealRongZhi"].ToString()) / 10;
                        detail.RealRemnant = double.Parse(reader["RealRemnant"].ToString()) / 10;
                        endP = (double.Parse(reader["ElectricPower"].ToString()) / 10)  > 0 ? (double.Parse(reader["ElectricPower"].ToString()) / 10) : endP;
                        // 按照30分钟的间隔读取detail数据
                        if (detail.RecordTime >= nextTime)
                        {
                            nextTime = nextTime.AddMinutes(30);
                            retList.Add(detail);
                            unFinished = false;
                        }
                        else
                        {
                            unFinished = true;
                        }
                    }
                    reader.Close();
                }
                // 记录剩余的数据
                if (unFinished)
                    retList.Add(detail);
                // 计算生产过程的用电量和效率值
                summary.PowerConsum = endP - beginP;
                this.GetProLineWeight2(ref summary);
                // 获得生产过程中的班组数据
                for (int i = 0; i < retList.Count; i++)
                {
                    TeamWorkTime team = GetTeamWorkByProLineTime(summary.ProductionLine, retList[i].RecordTime);
                    if (team != null)
                    {
                        retList[i].TeamName = team.TeamName;
                        retList[i].MixingerName = GetEmployeeName(team.MixingerName);
                        retList[i].PackerName = GetEmployeeName(team.PackerName);
                        retList[i].ExtruderName = GetEmployeeName(team.ExtruderName);
                        retList[i].QualityName = GetEmployeeName(team.QualityName);
                    }
                }
                retList.Sort((left, right) =>
                {
                    TimeSpan span = right.RecordTime - left.RecordTime;
                    return (int)span.TotalSeconds;
                });
                return retList;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetProductionReportDetail() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
                return null;
            }
        }
        private TeamWorkTime GetTeamWorkByProLineTime(string proline, DateTime workTime)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                TeamWorkTime retObj = null;
                string query = string.Format(GET_MEMBERID_BY_TIME, proline, workTime);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    if (reader.Read())
                    {
                        retObj = new TeamWorkTime();
                        string teamID = reader["TeamID"].ToString();
                        retObj.TeamName = teamID;
                        retObj.MixingerName = reader["MixingerID"].ToString();
                        retObj.ExtruderName = reader["ExtruderID"].ToString();
                        retObj.PackerName = reader["PackerID"].ToString();
                        retObj.QualityName = reader["QualityID"].ToString();
                        retObj.StartTime = DateTime.Parse(reader["StartTime"].ToString()); ;
                        retObj.EndTime = DateTime.Parse(reader["EndTime"].ToString());
                    }
                    reader.Close();
                }
                return retObj;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetTeamWorkByProLineTime() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
                return null;
            }
        }
        #endregion;

        #region 获得均化机报表数据
        public List<JHJReport> GetJHJReport(OrderCheckInfo order)
        {
            try
            {
                // 1. 保存用户选择的订单时间
                //SaveOrderTimeSelection(JHJName, order);
                List<JHJReport> retList = new List<JHJReport>();
                // 2. 获得指定的均化机数据
                List<JHJSummary> sumList = GetJHJSummary(order);
                if (sumList.Count == 0)
                    return retList;
                // 3. 获得均化机的详细数据
                foreach (JHJSummary summary in sumList)
                {
                    JHJReport obj = new JHJReport();
                    obj.Summary = summary;
                    obj.Summary.Number = 0;
                    obj.DataList = GetJHJDetail(summary);
                    foreach(JHJDetailInfo item in obj.DataList)
                    {
                        obj.Summary.Number += item.PackingNum;
                    }
                    retList.Add(obj);
                }
                return retList;
            }
            catch (Exception ex)
            {
                string msg = string.Format("SQLDataProcess.GetJHJReport() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                return null;
            }
        }
        private List<JHJSummary> GetJHJSummary(OrderCheckInfo order)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                List<JHJSummary> retList = new List<JHJSummary>();
                List<OrderCheckDetail> timeList = new List<OrderCheckDetail>();
                if (order.SelectSign == 1)
                    timeList = order.SystemRecord;
                else
                    timeList = order.ERPRecord;
                // 从DB中获得指定order的信息
                foreach (OrderCheckDetail item in timeList)
                {
                    JHJSummary summary = new JHJSummary();
                    summary.ProductionLine = item.ProductionLine;
                    summary.StartTime = item.StartTime;
                    summary.EndTime = item.EndTime;
                    summary.OrderID = order.OrderID;
                    summary.OrderString = order.OrderString;
                    summary.UpdateTime = item.UpdateTime;
                    // 从ERP中获得指定信息
                    ERPProductionInfo erpInfo = GetProductionInfo(summary.OrderID, item.ProductionLine);
                    if (erpInfo != null)
                    {
                        summary.OrderID = erpInfo.OrderID;
                        summary.ProductName = erpInfo.ProductName;
                        summary.TargetWeight = erpInfo.Count;
                        if (erpInfo.CurrCount > 0)
                        {
                            summary.CurrWeight = erpInfo.CurrCount;
                        }
                    }
                    if (summary.StartTime != null && summary.UpdateTime != null)
                    {
                        bool readedFirstLine = false;
                        double beginP = 0;
                        double endP = 0;
                        string query = string.Format(GET_JHJDETAILINFO_BY_TIME, summary.ProductionLine, summary.StartTime, summary.UpdateTime);
                        SqlConnection conn = new SqlConnection();
                        conn.ConnectionString = this.ConnString;
                        conn.Open();
                        using (cmd = new SqlCommand(query, conn))
                        {
                            cmd.CommandTimeout = 0;
                            reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                            while (reader.Read())
                            {
                                if (!readedFirstLine)
                                {
                                    summary.Number = int.Parse(reader["PackNumber"].ToString());
                                    endP = double.Parse(reader["ElectricPower"].ToString()) / 10;
                                    if (endP > 0)
                                        readedFirstLine = true;
                                }
                                else
                                {
                                    beginP = double.Parse(reader["ElectricPower"].ToString()) / 10;
                                }
                            }
                            reader.Close();
                        }
                        summary.PowerConsum = endP - beginP;
                        if (summary.CurrWeight != 0)
                            summary.EnergyConsum = summary.PowerConsum / summary.CurrWeight;
                        else if (summary.TargetWeight != 0)
                            summary.EnergyConsum = summary.PowerConsum / summary.TargetWeight;
                    }
                    if (summary != null)
                        retList.Add(summary);
                }
                return retList;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetJHJSummary() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
                return null;
            }
        }
        private List<JHJDetailInfo> GetJHJDetail(JHJSummary summary)
        {
            try
            {
                List<JHJDetailInfo> retList = new List<JHJDetailInfo>();
                DateTime startTime = (DateTime)summary.StartTime;
                DateTime? endTime = summary.UpdateTime;
                if (endTime == null)
                    return null;
                // 获得A桶至B桶吸料的数据列表
                List<ABRecordTime> abList = GetJHJTransDetail(summary.ProductionLine, startTime, (DateTime)endTime);
                // 获得B桶加热的数据列表
                List<BHeatingRecordTime> heatingList = GetJHJHeatingDetail(summary.ProductionLine, startTime, (DateTime)endTime);
                // 获得包装数据列表
                List<BPackingRecordTime> packingList = GetJHJPackingDetail(summary.ProductionLine, startTime, (DateTime)endTime);
                int i = 0;
                while (i < abList.Count || i < heatingList.Count || i < packingList.Count)
                {
                    JHJDetailInfo detail = new JHJDetailInfo();
                    if (i < abList.Count)
                    {
                        detail.ABStartTime = abList[i].StartTime;
                        detail.ABEndTime = abList[i].EndTime;
                        detail.ARealTemperature = abList[i].RealTemperature;
                    }
                    if (i < heatingList.Count)
                    {
                        detail.BHeatingStartTime = heatingList[i].StartTime;
                        detail.BHeatingEndTime = heatingList[i].EndTime;
                    }
                    if (i < packingList.Count)
                    {
                        detail.PackingStartTime = packingList[i].StartTime;
                        detail.PackingEndTime = packingList[i].EndTime;
                        detail.Temperature = packingList[i].Temperature;
                        detail.PackingNum = packingList[i].Number;
                        TeamWorkTime team = GetTeamWorkByProLineTime(summary.ProductionLine, (DateTime)detail.PackingStartTime);
                        if (team != null)
                        {
                            detail.TeamName = team.TeamName;
                            detail.PackerName = GetEmployeeName(team.PackerName);
                        }
                    }
                    retList.Add(detail);
                    i++;
                }

                return retList;
            }
            catch (Exception ex)
            {
                string msg = string.Format("SQLDataProcess.GetJHJDetail() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                return null;
            }
        }
        private List<ABRecordTime> GetJHJTransDetail(string productionLine, DateTime startTime, DateTime endTime)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                List<ABRecordTime> retList = new List<ABRecordTime>();
                string query = string.Format(GET_JHJTRANS_BY_PRODUCTIONLINE, productionLine, startTime, endTime);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        ABRecordTime obj = new ABRecordTime();
                        obj.StartTime = DateTime.Parse(reader["StartTime"].ToString());
                        if (!string.IsNullOrEmpty(reader["EndTime"].ToString()))
                        {
                            obj.EndTime = DateTime.Parse(reader["EndTime"].ToString());
                            TimeSpan span = (DateTime)obj.EndTime - obj.StartTime;
                            if (span.TotalSeconds > 60)
                                retList.Add(obj);
                        }
                    }
                    reader.Close();
                }
                for (int i = 0; i < retList.Count; i++)
                {
                    query = string.Format(GET_JHJDETAILINFO_BY_TIME, productionLine, retList[i].StartTime, retList[i].EndTime);
                    conn = new SqlConnection();
                    conn.ConnectionString = this.ConnString;
                    conn.Open();
                    using (cmd = new SqlCommand(query, conn))
                    {
                        cmd.CommandTimeout = 0;
                        reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                        if (reader.Read())
                            retList[i].RealTemperature = double.Parse(reader["ABoxTemper"].ToString()) / 10;
                        reader.Close();
                    }
                }
                retList.Sort((left, right) =>
                {
                    TimeSpan span = right.StartTime - left.StartTime;
                    return (int)span.TotalSeconds;
                });
                return retList;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetJHJTransDetail() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
                return null;
            }
        }
        private List<BHeatingRecordTime> GetJHJHeatingDetail(string productionLine, DateTime startTime, DateTime endTime)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                List<BHeatingRecordTime> retList = new List<BHeatingRecordTime>();
                string query = string.Format(GET_JHJHEATING_BY_PRODUCTIONLINE, productionLine, startTime, endTime);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        BHeatingRecordTime obj = new BHeatingRecordTime();
                        obj.StartTime = DateTime.Parse(reader["StartTime"].ToString());
                        if (!string.IsNullOrEmpty(reader["EndTime"].ToString()))
                        {
                            obj.EndTime = DateTime.Parse(reader["EndTime"].ToString());
                            TimeSpan span = (DateTime)obj.EndTime - obj.StartTime;
                            if (span.TotalSeconds > 60)
                                retList.Add(obj);
                        }
                    }
                    reader.Close();
                }
                retList.Sort((left, right) =>
                {
                    TimeSpan span = right.StartTime - left.StartTime;
                    return (int)span.TotalSeconds;
                });
                return retList;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetJHJHeatingDetail() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
                return null;
            }
        }
        private List<BPackingRecordTime> GetJHJPackingDetail(string productionLine, DateTime startTime, DateTime endTime)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                List<BPackingRecordTime> retList = new List<BPackingRecordTime>();
                string query = string.Format(GET_JHJPACK_BY_PRODUCTIONLINE, productionLine, startTime, endTime);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        BPackingRecordTime obj = new BPackingRecordTime();
                        obj.StartTime = DateTime.Parse(reader["StartTime"].ToString());
                        if (!string.IsNullOrEmpty(reader["EndTime"].ToString()))
                        {
                            obj.EndTime = DateTime.Parse(reader["EndTime"].ToString());
                            TimeSpan span = (DateTime)obj.EndTime - obj.StartTime;
                            if (span.TotalSeconds > 60)
                                retList.Add(obj);
                        }
                    }
                    reader.Close();
                }
                for (int i = 0; i < retList.Count; i++)
                {
                    int beginNum = 0;
                    int endNum = 0;
                    bool isFirstLine = true;
                    query = string.Format(GET_JHJDETAILINFO_BY_TIME, productionLine, retList[i].StartTime, retList[i].EndTime);
                    conn = new SqlConnection();
                    conn.ConnectionString = this.ConnString;
                    conn.Open();
                    using (cmd = new SqlCommand(query, conn))
                    {
                        cmd.CommandTimeout = 0;
                        reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                        while (reader.Read())
                        {
                            if (isFirstLine)
                            {
                                retList[i].Temperature = double.Parse(reader["BBoxZDTemper"].ToString()) / 10;
                                endNum = int.Parse(reader["PackNumber"].ToString());
                                isFirstLine = false;
                            }
                            beginNum = int.Parse(reader["PackNumber"].ToString());
                        }
                        reader.Close();
                    }
                    retList[i].Number = endNum - beginNum;
                    if (retList[i].Number == 0)
                    {
                        retList.RemoveAt(i);
                        i--;
                    }
                }
                retList.Sort((left, right) =>
                {
                    TimeSpan span = right.StartTime - left.StartTime;
                    return (int)span.TotalSeconds;
                });
                return retList;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetJHJPackingDetail() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
                return null;
            }
        }
        private List<TeamWorkTime> GetJHJTeamList(JHJReport obj)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                // 算出目前产品的时间段
                DateTime startTime = (DateTime)obj.Summary.StartTime;
                DateTime? endTime = obj.Summary.UpdateTime;
                if (endTime == null)
                    return null;
                List<TeamWorkTime> retList = new List<TeamWorkTime>();
                // 如果当前的订单不是刚刚开始, 算出订单生产中所参与的班组信息
                Dictionary<string, TeamWorkTime> dic = new Dictionary<string, TeamWorkTime>();
                // 算出每个班组在一个订单内的生产时间
                string query = string.Format(SELECT_TEAMWORKTIME_BY_TIME, obj.Summary.ProductionLine, startTime, endTime);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        DateTime workStartTime = DateTime.Parse(reader["StartTime"].ToString());
                        DateTime workEndTime = DateTime.Parse(reader["EndTime"].ToString());
                        workStartTime = (workStartTime > startTime) ? workStartTime : startTime;
                        workEndTime = (workEndTime < (DateTime)endTime) ? workEndTime : (DateTime)endTime;
                        TimeSpan span = (TimeSpan)(workEndTime - workStartTime);
                        string teamID = reader["TeamID"].ToString();
                        TeamWorkTime team = new TeamWorkTime();
                        team.TeamName = teamID;
                        team.MixingerName = reader["MixingerID"].ToString();
                        team.ExtruderName = reader["ExtruderID"].ToString();
                        team.PackerName = reader["PackerID"].ToString();
                        team.QualityName = reader["QualityID"].ToString();
                        team.StartTime = workStartTime;
                        team.EndTime = workEndTime;
                        retList.Add(team);
                    }
                    reader.Close();
                }
                return retList;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetJHJTeamList() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
                return null;
            }
        }
        private PRET.ReportDataDefine.DBData.JHJData GetJHJDataFromReader(SqlDataReader reader, string orderID, DateTime newStartTime, DateTime newEndTime)
        {
            PRET.ReportDataDefine.DBData.JHJData obj = new PRET.ReportDataDefine.DBData.JHJData();
            obj.OrderID = orderID;
            obj.StartTime = newStartTime;
            obj.EndTime = newEndTime;
            obj.UpdateTime = newEndTime;
            obj.ProductionLine = reader["ProductionLine"].ToString();
            obj.ProLineCode = reader["ProLineCode"].ToString();
            obj.Number = int.Parse(reader["Number"].ToString());
            obj.UnitWeight = float.Parse(reader["UnitWeight"].ToString());
            obj.StartPower = float.Parse(reader["StartPower"].ToString());
            obj.EndPower = float.Parse(reader["EndPower"].ToString());
            obj.MaxCurrent = float.Parse(reader["MaxCurrent"].ToString());
            obj.MinCurrent = float.Parse(reader["MinCurrent"].ToString());
            obj.MaxPower = float.Parse(reader["MaxPower"].ToString());
            return obj;
        }
        private PRET.ReportDataDefine.DBData.ABRecordTime GetJHJTransDataFromReader(SqlDataReader reader, DateTime newStartTime, DateTime newEndTime)
        {
            PRET.ReportDataDefine.DBData.ABRecordTime obj = new ReportDataDefine.DBData.ABRecordTime();
            obj.StartTime = newStartTime;
            obj.EndTime = newEndTime;
            obj.ProductionLine = reader["ProductionLine"].ToString();
            obj.ProLineCode = reader["ProLineCode"].ToString();
            obj.RealTemperature = double.Parse(reader["RealTemperature"].ToString());
            return obj;
        }
        private PRET.ReportDataDefine.DBData.BHeatingRecordTime GetJHJHeatingDataFromReader(SqlDataReader reader, string orderID, DateTime newStartTime, DateTime newEndTime)
        {
            PRET.ReportDataDefine.DBData.BHeatingRecordTime obj = new ReportDataDefine.DBData.BHeatingRecordTime();
            obj.OrderID = orderID;
            obj.StartTime = newStartTime;
            obj.EndTime = newEndTime;
            obj.ProductionLine = reader["ProductionLine"].ToString();
            obj.ProLineCode = reader["ProLineCode"].ToString();
            return obj;
        }
        private PRET.ReportDataDefine.DBData.BPackingRecordTime GetJHJPackDataFromReader(SqlDataReader reader, string orderID, DateTime newStartTime, DateTime newEndTime)
        {
            PRET.ReportDataDefine.DBData.BPackingRecordTime obj = new ReportDataDefine.DBData.BPackingRecordTime();
            obj.OrderID = orderID;
            obj.StartTime = newStartTime;
            obj.EndTime = newEndTime;
            obj.ProductionLine = reader["ProductionLine"].ToString();
            obj.ProLineCode = reader["ProLineCode"].ToString();
            obj.Temperature = double.Parse(reader["Temperature"].ToString());
            obj.StartNum = int.Parse(reader["StartNum"].ToString());
            obj.EndNum = int.Parse(reader["EndNum"].ToString());
            return obj;
        }


        private DateTime? GetLatestEndTime(JHJDetailInfo info)
        {
            DateTime? endTime = null;
            if (info.PackingEndTime != null)
            {
                endTime = info.PackingEndTime;
            }
            else if (info.BHeatingEndTime != null)
            {
                endTime = info.BHeatingEndTime;
            }
            else if (info.ABEndTime != null)
            {
                endTime = info.ABEndTime;
            }
            return endTime;
        }
        #endregion

        #region 获得电表数据
        private readonly static TimeSpan GenStartSpan1 = TimeSpan.Parse("7:00");
        private readonly static TimeSpan GenEndSpan1 = TimeSpan.Parse("9:59:59");
        private readonly static TimeSpan GenStartSpan2 = TimeSpan.Parse("15:00");
        private readonly static TimeSpan GenEndSpan2 = TimeSpan.Parse("17:59:59");
        private readonly static TimeSpan GenStartSpan3 = TimeSpan.Parse("21:00");
        private readonly static TimeSpan GenEndSpan3 = TimeSpan.Parse("22:59:59");
        private readonly static TimeSpan PeakStartSpan1 = TimeSpan.Parse("10:00");
        private readonly static TimeSpan PeakEndSpan1 = TimeSpan.Parse("14:59:59");
        private readonly static TimeSpan PeakStartSpan2 = TimeSpan.Parse("18:00");
        private readonly static TimeSpan PeakEndSpan2 = TimeSpan.Parse("20:59:59");
        private readonly static TimeSpan LowStartSpan1 = TimeSpan.Parse("23:00");
        private readonly static TimeSpan LowEndSpan1 = TimeSpan.Parse("23:59:59");
        private readonly static TimeSpan LowStartSpan2 = TimeSpan.Parse("00:00");
        private readonly static TimeSpan LowEndSpan2 = TimeSpan.Parse("6:59:59");
        private enum MeterRanger
        {
            LOW = 1,
            GEN,
            PEAK
        }

        public List<float> GetMeterUnitPrice()
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                List<float> retList = new List<float>();
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(GET_METERDATA, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    if (reader.Read())
                    {
                        float peak = float.Parse(reader["PeakUnitPrice"].ToString());
                        float gen = float.Parse(reader["GenUnitPrice"].ToString());
                        float low = float.Parse(reader["LowUnitPrice"].ToString());
                        retList.Add(peak);
                        retList.Add(gen);
                        retList.Add(low);
                    }
                    reader.Close();
                }
                return retList;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetMeterUnitPrice() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
                return null;
            }
        }
        public MeterReadingReport GetMeterReadingReport(DateTime startTime, DateTime endTime, float peakPrice, float genPrice, float lowPrice)
        {
            try
            {
                MeterReadingReport obj = new MeterReadingReport();
                // 设置电表summary信息
                obj.Summary = GetMeterReadingSummary(startTime, endTime, peakPrice, genPrice, lowPrice);
                // 获得所有生产线电表的详细数据
                obj.DataList = GetMeterReadingDetails(startTime, endTime, peakPrice, genPrice, lowPrice);
                return obj;
            }
            catch (Exception ex)
            {
                string msg = string.Format("SQLDataProcess.GetMeterReadingReport() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                return null;
            }
        }
        private MeterReadingSummary GetMeterReadingSummary(DateTime startTime, DateTime endTime, float peakPrice, float genPrice, float lowPrice)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                bool isExist = false;
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(GET_METERDATA, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    if (reader.Read())
                        isExist = true;
                    reader.Close();
                }
                string sql = "";
                if (isExist)
                {
                    sql = string.Format(UPDATE_METERDATA, peakPrice, genPrice, lowPrice);
                }
                else
                {
                    sql = string.Format(INSERT_METERDATA, peakPrice, genPrice, lowPrice);
                }
                conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(sql, conn))
                {
                    cmd.CommandTimeout = 0;
                    cmd.ExecuteNonQuery();
                }
                conn.Close();
                MeterReadingSummary summary = new MeterReadingSummary();
                summary.StartTime = startTime;
                summary.EndTime = endTime;
                summary.PeakUnitPrice = peakPrice;
                summary.GenUnitPrice = genPrice;
                summary.LowUnitPrice = lowPrice;
                return summary;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetMeterReadingSummary() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
                return null;
            }
        }
        private List<MeterReadingDetailInfo> GetMeterReadingDetails(DateTime startTime, DateTime endTime, float peakPrice, float genPrice, float lowPrice)
        {
            try
            {
                //ReConnectSQL();
                List<MeterReadingDetailInfo> retList = new List<MeterReadingDetailInfo>();
                // 1. 获得所有生产线
                //List<string> proLines = new List<string>();
                //using (cmd = new SqlCommand(GET_ALL_PRODUCTIONLINE, conn))
                //{
                //    reader = cmd.ExecuteReader();
                //    while (reader.Read())
                //    {
                //        if (!string.IsNullOrEmpty(reader["ProductionLine"].ToString()))
                //            proLines.Add(reader["ProductionLine"].ToString());
                //    }
                //    reader.Close();
                //}
                //if (proLines.Count == 0)
                //    return retList;
                //proLines.Sort((left, right) =>
                //{
                //    int num1 = int.Parse(left.Substring(1));
                //    int num2 = int.Parse(right.Substring(1));
                //    return num1 - num2;
                //});
                // 1. 获得当前所有生产线
                List<string> proLines = this.GetAllProductionLineNames();
                //int proNo = 1;
                foreach (string proline in proLines)
                {
                    MeterReadingDetailInfo meterObj = new MeterReadingDetailInfo();

                    //获得生产线上挤出机指定天数的电表数据
                    List<MeterDetailInfo> JCList = GetJCJMeterDetailList(proline, startTime, endTime);
                    List<MeterDetailInfo> JHList = GetJHJMeterDetailList(proline, startTime, endTime);

                    MeterReadingDetailInfo JCMeterObj = new MeterReadingDetailInfo();
                    JCMeterObj = GetJCJMeterDetailInfo(JCList, proline, peakPrice, genPrice, lowPrice);
                    MeterReadingDetailInfo JHMeterObj = new MeterReadingDetailInfo();
                    JHMeterObj = GetJHJMeterDetailInfo(JHList, proline, peakPrice, genPrice, lowPrice);
                    if (JCMeterObj != null && JHMeterObj != null)
                    {
                        meterObj.AmmeterID = proline;
                        meterObj.JCPeakConsumption = JCMeterObj.JCPeakConsumption;
                        meterObj.JHPeakConsumption = JHMeterObj.JHPeakConsumption;
                        meterObj.PeakConsumption = JCMeterObj.JCPeakConsumption + JHMeterObj.JHPeakConsumption;
                        meterObj.JCAvgConsumption = JCMeterObj.JCAvgConsumption;
                        meterObj.JHAvgConsumption = JHMeterObj.JHAvgConsumption;
                        meterObj.AvgConsumption = JCMeterObj.JCAvgConsumption + JHMeterObj.JHAvgConsumption;
                        meterObj.JCValleyConsumption = JCMeterObj.JCValleyConsumption;
                        meterObj.JHValleyConsumption = JHMeterObj.JHValleyConsumption;
                        meterObj.ValleyConsumption = JCMeterObj.JCValleyConsumption + JHMeterObj.JHValleyConsumption;
                        meterObj.JCElectricCharge = JCMeterObj.JCElectricCharge;
                        meterObj.JHElectricCharge = JHMeterObj.JHElectricCharge;
                        meterObj.ElectricCharge = JCMeterObj.JCElectricCharge + JHMeterObj.JHElectricCharge;
                        meterObj.JCMaxCurrent = JCMeterObj.JCMaxCurrent;
                        meterObj.JHMaxCurrent = JHMeterObj.JHMaxCurrent;
                        meterObj.MaxCurrent = JCMeterObj.JCMaxCurrent + JHMeterObj.JHMaxCurrent;
                        meterObj.JCMinCurrent = JCMeterObj.JCMinCurrent;
                        meterObj.JHMinCurrent = JHMeterObj.JHMinCurrent;
                        meterObj.MinCurrent = JCMeterObj.JCMinCurrent + JHMeterObj.JHMinCurrent;
                        meterObj.JCMaxLoad = JCMeterObj.JCMaxLoad;
                        meterObj.JHMaxLoad = JHMeterObj.JHMaxLoad;
                        meterObj.MaxLoad = JCMeterObj.JCMaxLoad + JHMeterObj.JHMaxLoad;
                    }
                    else if (JCMeterObj != null)
                        meterObj = JCMeterObj;
                    else if (JHMeterObj != null)
                        meterObj = JHMeterObj;


                    //// 计算总共的天数
                    //TimeSpan span = (TimeSpan)(endTime - startTime);
                    //// 跳过A1 - A4
                    //if (proNo++ >= 5)
                    //{
                    //    for (int i = 0; i < span.TotalDays; i++)
                    //    {
                    //        // 计算LowStartSpan2 - LowEndSpan2 (00:00:00 - 06:59:59)之间的耗电量
                    //        DateTime s1 = startTime.AddDays(i).AddSeconds(LowStartSpan2.TotalSeconds);
                    //        DateTime e1 = startTime.AddDays(i).AddSeconds(LowEndSpan2.TotalSeconds);
                    //        GetJCJMeterDetailInfo(proline, s1, e1, lowPrice, ref meterObj, MeterRanger.LOW);
                    //        GetJHJMeterDetailInfo(proline, s1, e1, lowPrice, ref meterObj, MeterRanger.LOW);
                    //        // 计算GenStartSpan - GenEndSpan (07:00:00 - 09:59:59)之间的耗电量
                    //        s1 = startTime.AddDays(i).AddSeconds(GenStartSpan1.TotalSeconds);
                    //        e1 = startTime.AddDays(i).AddSeconds(GenEndSpan1.TotalSeconds);
                    //        GetJCJMeterDetailInfo(proline, s1, e1, lowPrice, ref meterObj, MeterRanger.GEN);
                    //        GetJHJMeterDetailInfo(proline, s1, e1, lowPrice, ref meterObj, MeterRanger.GEN);
                    //        // 计算PeakStartSpan - PeakEndSpan (10:00:00 - 14:59:59)之间的耗电量
                    //        s1 = startTime.AddDays(i).AddSeconds(PeakStartSpan1.TotalSeconds);
                    //        e1 = startTime.AddDays(i).AddSeconds(PeakEndSpan1.TotalSeconds);
                    //        GetJCJMeterDetailInfo(proline, s1, e1, peakPrice, ref meterObj, MeterRanger.PEAK);
                    //        GetJHJMeterDetailInfo(proline, s1, e1, peakPrice, ref meterObj, MeterRanger.PEAK);
                    //        // 计算GenStartSpan - GenEndSpan (15:00:00 - 17:59:59)之间的耗电量
                    //        s1 = startTime.AddDays(i).AddSeconds(GenStartSpan2.TotalSeconds);
                    //        e1 = startTime.AddDays(i).AddSeconds(GenEndSpan2.TotalSeconds);
                    //        GetJCJMeterDetailInfo(proline, s1, e1, lowPrice, ref meterObj, MeterRanger.GEN);
                    //        GetJHJMeterDetailInfo(proline, s1, e1, lowPrice, ref meterObj, MeterRanger.GEN);
                    //        // 计算PeakStartSpan - PeakEndSpan (18:00:00 - 20:59:59)之间的耗电量
                    //        s1 = startTime.AddDays(i).AddSeconds(PeakStartSpan2.TotalSeconds);
                    //        e1 = startTime.AddDays(i).AddSeconds(PeakEndSpan2.TotalSeconds);
                    //        GetJCJMeterDetailInfo(proline, s1, e1, peakPrice, ref meterObj, MeterRanger.PEAK);
                    //        GetJHJMeterDetailInfo(proline, s1, e1, peakPrice, ref meterObj, MeterRanger.PEAK);
                    //        // 计算GenStartSpan - GenEndSpan (21:00:00 - 22:59:59)之间的耗电量
                    //        s1 = startTime.AddDays(i).AddSeconds(GenStartSpan3.TotalSeconds);
                    //        e1 = startTime.AddDays(i).AddSeconds(GenEndSpan3.TotalSeconds);
                    //        GetJCJMeterDetailInfo(proline, s1, e1, genPrice, ref meterObj, MeterRanger.GEN);
                    //        GetJHJMeterDetailInfo(proline, s1, e1, genPrice, ref meterObj, MeterRanger.GEN);
                    //        // 计算LowStartSpan1 - LowEndSpan1 (23:00:00 - 23:59:59)之间的耗电量
                    //        s1 = startTime.AddDays(i).AddSeconds(LowStartSpan1.TotalSeconds);
                    //        e1 = startTime.AddDays(i).AddSeconds(LowEndSpan1.TotalSeconds);
                    //        GetJCJMeterDetailInfo(proline, s1, e1, lowPrice, ref meterObj, MeterRanger.LOW);
                    //        GetJHJMeterDetailInfo(proline, s1, e1, lowPrice, ref meterObj, MeterRanger.LOW);
                    //    }
                    //}
                    if (meterObj != null)
                        retList.Add(meterObj);
                }
                return retList;
            }
            catch (Exception ex)
            {
                string msg = string.Format("SQLDataProcess.GetMeterReadingDetails() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
                return null;
            }
        }
        private List<MeterDetailInfo> GetJCJMeterDetailList(string proline, DateTime startTime, DateTime endTime)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                List<MeterDetailInfo> retList = new List<MeterDetailInfo>();
                string query = string.Format(GET_JCJPOWER_BY_TIME, proline, startTime, endTime);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        MeterDetailInfo obj = new MeterDetailInfo();
                        obj.RecordTime = DateTime.Parse(reader["RecordTime"].ToString());
                        obj.ElectricPower = double.Parse(reader["ElectricPower"].ToString()) / 10;
                        obj.MaxCurrent = double.Parse(reader["MaxCurrent"].ToString()) / 10;
                        obj.MinCurrent = double.Parse(reader["MinCurrent"].ToString()) / 10;
                        obj.MaxPower = double.Parse(reader["MaxPower"].ToString()) / 10;
                        retList.Add(obj);
                    }
                }
                return retList;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetJCJMeterDetailList() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                return null;
            }
        }
        private List<MeterDetailInfo> GetJHJMeterDetailList(string proline, DateTime startTime, DateTime endTime)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                List<MeterDetailInfo> retList = new List<MeterDetailInfo>();
                string query = string.Format(GET_JHJPOWER_BY_TIME, proline, startTime, endTime);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        MeterDetailInfo obj = new MeterDetailInfo();
                        obj.RecordTime = DateTime.Parse(reader["RecordTime"].ToString());
                        obj.ElectricPower = double.Parse(reader["ElectricPower"].ToString()) / 10;
                        obj.MaxCurrent = double.Parse(reader["MaxCurrent"].ToString()) / 10;
                        obj.MinCurrent = double.Parse(reader["MinCurrent"].ToString()) / 10;
                        obj.MaxPower = double.Parse(reader["MaxPower"].ToString()) / 10;
                        retList.Add(obj);
                    }
                }
                return retList;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetJCJMeterDetailList() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                return null;
            }
        }
        private MeterReadingDetailInfo GetJCJMeterDetailInfo(List<MeterDetailInfo> meterList, string proline, float peakPrice, float genPrice, float lowPrice)
        {
            MeterReadingDetailInfo obj = new MeterReadingDetailInfo();
            obj.AmmeterID = proline;
            obj.AmmeterPlace = proline + "-" + "挤出机";
            if (meterList != null && meterList.Count > 0)
            {
                bool low1 = false, low2 = false;
                bool gen1 = false, gen2 = false, gen3 = false;
                bool peak1 = false, peak2 = false;
                DateTime stime = DateTime.Now;
                DateTime etime = DateTime.Now;
                double maxCurrent = double.MinValue;
                double minCurrent = double.MaxValue;
                double maxPower = double.MinValue;
                double beginP = 0;
                double endP = 0;
                foreach(MeterDetailInfo item in meterList)
                {
                    maxCurrent = maxCurrent < item.MaxCurrent ? item.MaxCurrent : maxCurrent;
                    minCurrent = minCurrent > item.MinCurrent ? item.MinCurrent : minCurrent;
                    maxPower = maxPower < item.MaxPower ? item.MaxPower : maxPower;
                    TimeSpan span = item.RecordTime.TimeOfDay;
                    // 如果记录时间在一个时间段内
                    if (LowStartSpan2 <= span && span <= LowEndSpan2)
                    {
                        // 且这个时间段为刚刚进入的时间段
                        if (low2 == false)
                        {
                            low2 = true;
                            // 把之前保存的数据信息写入到相应的时间段内
                            if (low1)
                                obj.JCValleyConsumption += endP - beginP;
                            else if (gen1 || gen2 || gen3)
                                obj.JCAvgConsumption += endP - beginP;
                            else if (peak1 || peak2)
                                obj.JCPeakConsumption += endP - beginP;
                            low1 = false;
                            gen1 = false; gen2 = false; gen3 = false; 
                            peak1 = false; peak2 = false;
                            // 初始化开始和结束的值
                            beginP = item.ElectricPower;
                            endP = item.ElectricPower;
                            stime = item.RecordTime;
                            etime = item.RecordTime;
                        }
                        else // 如果是连续的进入一个时间段
                        {
                            // 如果间隔大于12小时，则认为是一个新的时间段，需要把以前的时间段内的数据保存起来
                            TimeSpan interval = item.RecordTime - stime;
                            if (interval.TotalHours > 12)
                            {
                                obj.JCValleyConsumption += endP - beginP;
                                beginP = item.ElectricPower;
                                endP = item.ElectricPower;
                                stime = item.RecordTime;
                                etime = item.RecordTime;
                            }
                            else //如果是连续进入一个时间段，持续更新最后的结束时间和电量值
                            {
                                endP = item.ElectricPower;
                                etime = item.RecordTime;
                            }
                        }
                    }
                    else if (LowStartSpan1 <= span && span <= LowEndSpan1)
                    {
                        // 且这个时间段为刚刚进入的时间段
                        if (low1 == false)
                        {
                            low1 = true;
                            // 把之前保存的数据信息写入到相应的时间段内
                            if (low2)
                                obj.JCValleyConsumption += endP - beginP;
                            else if (gen1 || gen2 || gen3)
                                obj.JCAvgConsumption += endP - beginP;
                            else if (peak1 || peak2)
                                obj.JCPeakConsumption += endP - beginP;
                            low2 = false;
                            gen1 = false; gen2 = false; gen3 = false;
                            peak1 = false; peak2 = false;
                            // 初始化开始和结束的值
                            beginP = item.ElectricPower;
                            endP = item.ElectricPower;
                            stime = item.RecordTime;
                            etime = item.RecordTime;
                        }
                        else // 如果是连续的进入一个时间段
                        {
                            // 如果间隔大于12小时，则认为是一个新的时间段，需要把以前的时间段内的数据保存起来
                            TimeSpan interval = item.RecordTime - stime;
                            if (interval.TotalHours > 12)
                            {
                                obj.JCValleyConsumption += endP - beginP;
                                beginP = item.ElectricPower;
                                endP = item.ElectricPower;
                                stime = item.RecordTime;
                                etime = item.RecordTime;
                            }
                            else //如果是连续进入一个时间段，持续更新最后的结束时间和电量值
                            {
                                endP = item.ElectricPower;
                                etime = item.RecordTime;
                            }
                        }
                    }
                    else if (GenStartSpan1 <= span && span <= GenEndSpan1)
                    {
                        // 且这个时间段为刚刚进入的时间段
                        if (gen1 == false)
                        {
                            gen1 = true;
                            // 把之前保存的数据信息写入到相应的时间段内
                            if (low1 || low2)
                                obj.JCValleyConsumption += endP - beginP;
                            else if (gen2 || gen3)
                                obj.JCAvgConsumption += endP - beginP;
                            else if (peak1 || peak2)
                                obj.JCPeakConsumption += endP - beginP;
                            low1 = false; low2 = false;
                            gen2 = false; gen3 = false;
                            peak1 = false; peak2 = false;
                            // 初始化开始和结束的值
                            beginP = item.ElectricPower;
                            endP = item.ElectricPower;
                            stime = item.RecordTime;
                            etime = item.RecordTime;
                        }
                        else // 如果是连续的进入一个时间段
                        {
                            // 如果间隔大于12小时，则认为是一个新的时间段，需要把以前的时间段内的数据保存起来
                            TimeSpan interval = item.RecordTime - stime;
                            if (interval.TotalHours > 12)
                            {
                                obj.JCAvgConsumption += endP - beginP;
                                beginP = item.ElectricPower;
                                endP = item.ElectricPower;
                                stime = item.RecordTime;
                                etime = item.RecordTime;
                            }
                            else //如果是连续进入一个时间段，持续更新最后的结束时间和电量值
                            {
                                endP = item.ElectricPower;
                                etime = item.RecordTime;
                            }
                        }
                    }
                    else if (GenStartSpan2 <= span && span <= GenEndSpan2)
                    {
                        // 且这个时间段为刚刚进入的时间段
                        if (gen2 == false)
                        {
                            gen2 = true;
                            // 把之前保存的数据信息写入到相应的时间段内
                            if (low1 || low2)
                                obj.JCValleyConsumption += endP - beginP;
                            else if (gen1 || gen3)
                                obj.JCAvgConsumption += endP - beginP;
                            else if (peak1 || peak2)
                                obj.JCPeakConsumption += endP - beginP;
                            low1 = false; low2 = false;
                            gen1 = false; gen3 = false;
                            peak1 = false; peak2 = false;
                            // 初始化开始和结束的值
                            beginP = item.ElectricPower;
                            endP = item.ElectricPower;
                            stime = item.RecordTime;
                            etime = item.RecordTime;
                        }
                        else // 如果是连续的进入一个时间段
                        {
                            // 如果间隔大于12小时，则认为是一个新的时间段，需要把以前的时间段内的数据保存起来
                            TimeSpan interval = item.RecordTime - stime;
                            if (interval.TotalHours > 12)
                            {
                                obj.JCAvgConsumption += endP - beginP;
                                beginP = item.ElectricPower;
                                endP = item.ElectricPower;
                                stime = item.RecordTime;
                                etime = item.RecordTime;
                            }
                            else //如果是连续进入一个时间段，持续更新最后的结束时间和电量值
                            {
                                endP = item.ElectricPower;
                                etime = item.RecordTime;
                            }
                        }
                    }
                    else if (GenStartSpan3 <= span && span <= GenEndSpan3)
                    {
                        // 且这个时间段为刚刚进入的时间段
                        if (gen3 == false)
                        {
                            gen3 = true;
                            // 把之前保存的数据信息写入到相应的时间段内
                            if (low1 || low2)
                                obj.JCValleyConsumption += endP - beginP;
                            else if (gen1 || gen2)
                                obj.JCAvgConsumption += endP - beginP;
                            else if (peak1 || peak2)
                                obj.JCPeakConsumption += endP - beginP;
                            low1 = false; low2 = false;
                            gen1 = false; gen2 = false;
                            peak1 = false; peak2 = false;
                            // 初始化开始和结束的值
                            beginP = item.ElectricPower;
                            endP = item.ElectricPower;
                            stime = item.RecordTime;
                            etime = item.RecordTime;
                        }
                        else // 如果是连续的进入一个时间段
                        {
                            // 如果间隔大于12小时，则认为是一个新的时间段，需要把以前的时间段内的数据保存起来
                            TimeSpan interval = item.RecordTime - stime;
                            if (interval.TotalHours > 12)
                            {
                                obj.JCAvgConsumption += endP - beginP;
                                beginP = item.ElectricPower;
                                endP = item.ElectricPower;
                                stime = item.RecordTime;
                                etime = item.RecordTime;
                            }
                            else //如果是连续进入一个时间段，持续更新最后的结束时间和电量值
                            {
                                endP = item.ElectricPower;
                                etime = item.RecordTime;
                            }
                        }
                    }
                    else if (PeakStartSpan1 <= span && span <= PeakEndSpan1)
                    {
                        // 且这个时间段为刚刚进入的时间段
                        if (peak1 == false)
                        {
                            peak1 = true;
                            // 把之前保存的数据信息写入到相应的时间段内
                            if (low1 || low2)
                                obj.JCValleyConsumption += endP - beginP;
                            else if (gen1 || gen2 || gen3)
                                obj.JCAvgConsumption += endP - beginP;
                            else if (peak2)
                                obj.JCPeakConsumption += endP - beginP;
                            low1 = false; low2 = false;
                            gen1 = false; gen2 = false; gen3 = false;
                            peak2 = false;
                            // 初始化开始和结束的值
                            beginP = item.ElectricPower;
                            endP = item.ElectricPower;
                            stime = item.RecordTime;
                            etime = item.RecordTime;
                        }
                        else // 如果是连续的进入一个时间段
                        {
                            // 如果间隔大于12小时，则认为是一个新的时间段，需要把以前的时间段内的数据保存起来
                            TimeSpan interval = item.RecordTime - stime;
                            if (interval.TotalHours > 12)
                            {
                                obj.JCPeakConsumption += endP - beginP;
                                beginP = item.ElectricPower;
                                endP = item.ElectricPower;
                                stime = item.RecordTime;
                                etime = item.RecordTime;
                            }
                            else //如果是连续进入一个时间段，持续更新最后的结束时间和电量值
                            {
                                endP = item.ElectricPower;
                                etime = item.RecordTime;
                            }
                        }
                    }
                    else if (PeakStartSpan2 <= span && span <= PeakEndSpan2)
                    {
                        // 且这个时间段为刚刚进入的时间段
                        if (peak2 == false)
                        {
                            peak2 = true;
                            // 把之前保存的数据信息写入到相应的时间段内
                            if (low1 || low2)
                                obj.JCValleyConsumption += endP - beginP;
                            else if (gen1 || gen2 || gen3)
                                obj.JCAvgConsumption += endP - beginP;
                            else if (peak1)
                                obj.JCPeakConsumption += endP - beginP;
                            low1 = false; low2 = false;
                            gen1 = false; gen2 = false; gen3 = false;
                            peak1 = false;
                            // 初始化开始和结束的值
                            beginP = item.ElectricPower;
                            endP = item.ElectricPower;
                            stime = item.RecordTime;
                            etime = item.RecordTime;
                        }
                        else // 如果是连续的进入一个时间段
                        {
                            // 如果间隔大于12小时，则认为是一个新的时间段，需要把以前的时间段内的数据保存起来
                            TimeSpan interval = item.RecordTime - stime;
                            if (interval.TotalHours > 12)
                            {
                                obj.JCPeakConsumption += endP - beginP;
                                beginP = item.ElectricPower;
                                endP = item.ElectricPower;
                                stime = item.RecordTime;
                                etime = item.RecordTime;
                            }
                            else //如果是连续进入一个时间段，持续更新最后的结束时间和电量值
                            {
                                endP = item.ElectricPower;
                                etime = item.RecordTime;
                            }
                        }
                    }
                }
                obj.JCMaxCurrent = maxCurrent == double.MinValue ? 0 : maxCurrent;
                obj.JCMinCurrent = minCurrent == double.MaxValue ? 0 : minCurrent;
                obj.JCMaxLoad = maxPower == double.MinValue ? 0 : maxPower;
                obj.JCPeakConsumption = Math.Round(obj.JCPeakConsumption, 0);
                obj.JCAvgConsumption = Math.Round(obj.JCAvgConsumption, 0);
                obj.JCValleyConsumption = Math.Round(obj.JCValleyConsumption, 0);
                obj.JCElectricCharge = obj.JCPeakConsumption * peakPrice + obj.JCAvgConsumption * genPrice + obj.JCValleyConsumption * lowPrice;
            }
            return obj;
        }
        private MeterReadingDetailInfo GetJHJMeterDetailInfo(List<MeterDetailInfo> meterList, string proline, float peakPrice, float genPrice, float lowPrice)
        {
            MeterReadingDetailInfo obj = new MeterReadingDetailInfo();
            obj.AmmeterID = proline;
            obj.AmmeterPlace = proline + "-" + "均化机";
            if (meterList != null && meterList.Count > 0)
            {
                bool low1 = false, low2 = false;
                bool gen1 = false, gen2 = false, gen3 = false;
                bool peak1 = false, peak2 = false;
                DateTime stime = DateTime.Now;
                DateTime etime = DateTime.Now;
                double maxCurrent = double.MinValue;
                double minCurrent = double.MaxValue;
                double maxPower = double.MinValue;
                double beginP = 0;
                double endP = 0;
                foreach (MeterDetailInfo item in meterList)
                {
                    maxCurrent = maxCurrent < item.MaxCurrent ? item.MaxCurrent : maxCurrent;
                    minCurrent = minCurrent > item.MinCurrent ? item.MinCurrent : minCurrent;
                    maxPower = maxPower < item.MaxPower ? item.MaxPower : maxPower;
                    TimeSpan span = item.RecordTime.TimeOfDay;
                    // 如果记录时间在一个时间段内
                    if (LowStartSpan2 <= span && span <= LowEndSpan2)
                    {
                        // 且这个时间段为刚刚进入的时间段
                        if (low2 == false)
                        {
                            low2 = true;
                            // 把之前保存的数据信息写入到相应的时间段内
                            if (low1)
                                obj.JHValleyConsumption += endP - beginP;
                            else if (gen1 || gen2 || gen3)
                                obj.JHAvgConsumption += endP - beginP;
                            else if (peak1 || peak2)
                                obj.JHPeakConsumption += endP - beginP;
                            low1 = false;
                            gen1 = false; gen2 = false; gen3 = false;
                            peak1 = false; peak2 = false;
                            // 初始化开始和结束的值
                            beginP = item.ElectricPower;
                            endP = item.ElectricPower;
                            stime = item.RecordTime;
                            etime = item.RecordTime;
                        }
                        else // 如果是连续的进入一个时间段
                        {
                            // 如果间隔大于12小时，则认为是一个新的时间段，需要把以前的时间段内的数据保存起来
                            TimeSpan interval = item.RecordTime - stime;
                            if (interval.TotalHours > 12)
                            {
                                obj.JHValleyConsumption += endP - beginP;
                                beginP = item.ElectricPower;
                                endP = item.ElectricPower;
                                stime = item.RecordTime;
                                etime = item.RecordTime;
                            }
                            else //如果是连续进入一个时间段，持续更新最后的结束时间和电量值
                            {
                                endP = item.ElectricPower;
                                etime = item.RecordTime;
                            }
                        }
                    }
                    else if (LowStartSpan1 <= span && span <= LowEndSpan1)
                    {
                        // 且这个时间段为刚刚进入的时间段
                        if (low1 == false)
                        {
                            low1 = true;
                            // 把之前保存的数据信息写入到相应的时间段内
                            if (low2)
                                obj.JHValleyConsumption += endP - beginP;
                            else if (gen1 || gen2 || gen3)
                                obj.JHAvgConsumption += endP - beginP;
                            else if (peak1 || peak2)
                                obj.JHPeakConsumption += endP - beginP;
                            low2 = false;
                            gen1 = false; gen2 = false; gen3 = false;
                            peak1 = false; peak2 = false;
                            // 初始化开始和结束的值
                            beginP = item.ElectricPower;
                            endP = item.ElectricPower;
                            stime = item.RecordTime;
                            etime = item.RecordTime;
                        }
                        else // 如果是连续的进入一个时间段
                        {
                            // 如果间隔大于12小时，则认为是一个新的时间段，需要把以前的时间段内的数据保存起来
                            TimeSpan interval = item.RecordTime - stime;
                            if (interval.TotalHours > 12)
                            {
                                obj.JHValleyConsumption += endP - beginP;
                                beginP = item.ElectricPower;
                                endP = item.ElectricPower;
                                stime = item.RecordTime;
                                etime = item.RecordTime;
                            }
                            else //如果是连续进入一个时间段，持续更新最后的结束时间和电量值
                            {
                                endP = item.ElectricPower;
                                etime = item.RecordTime;
                            }
                        }
                    }
                    else if (GenStartSpan1 <= span && span <= GenEndSpan1)
                    {
                        // 且这个时间段为刚刚进入的时间段
                        if (gen1 == false)
                        {
                            gen1 = true;
                            // 把之前保存的数据信息写入到相应的时间段内
                            if (low1 || low2)
                                obj.JHValleyConsumption += endP - beginP;
                            else if (gen2 || gen3)
                                obj.JHAvgConsumption += endP - beginP;
                            else if (peak1 || peak2)
                                obj.JHPeakConsumption += endP - beginP;
                            low1 = false; low2 = false;
                            gen2 = false; gen3 = false;
                            peak1 = false; peak2 = false;
                            // 初始化开始和结束的值
                            beginP = item.ElectricPower;
                            endP = item.ElectricPower;
                            stime = item.RecordTime;
                            etime = item.RecordTime;
                        }
                        else // 如果是连续的进入一个时间段
                        {
                            // 如果间隔大于12小时，则认为是一个新的时间段，需要把以前的时间段内的数据保存起来
                            TimeSpan interval = item.RecordTime - stime;
                            if (interval.TotalHours > 12)
                            {
                                obj.JHAvgConsumption += endP - beginP;
                                beginP = item.ElectricPower;
                                endP = item.ElectricPower;
                                stime = item.RecordTime;
                                etime = item.RecordTime;
                            }
                            else //如果是连续进入一个时间段，持续更新最后的结束时间和电量值
                            {
                                endP = item.ElectricPower;
                                etime = item.RecordTime;
                            }
                        }
                    }
                    else if (GenStartSpan2 <= span && span <= GenEndSpan2)
                    {
                        // 且这个时间段为刚刚进入的时间段
                        if (gen2 == false)
                        {
                            gen2 = true;
                            // 把之前保存的数据信息写入到相应的时间段内
                            if (low1 || low2)
                                obj.JHValleyConsumption += endP - beginP;
                            else if (gen1 || gen3)
                                obj.JHAvgConsumption += endP - beginP;
                            else if (peak1 || peak2)
                                obj.JHPeakConsumption += endP - beginP;
                            low1 = false; low2 = false;
                            gen1 = false; gen3 = false;
                            peak1 = false; peak2 = false;
                            // 初始化开始和结束的值
                            beginP = item.ElectricPower;
                            endP = item.ElectricPower;
                            stime = item.RecordTime;
                            etime = item.RecordTime;
                        }
                        else // 如果是连续的进入一个时间段
                        {
                            // 如果间隔大于12小时，则认为是一个新的时间段，需要把以前的时间段内的数据保存起来
                            TimeSpan interval = item.RecordTime - stime;
                            if (interval.TotalHours > 12)
                            {
                                obj.JHAvgConsumption += endP - beginP;
                                beginP = item.ElectricPower;
                                endP = item.ElectricPower;
                                stime = item.RecordTime;
                                etime = item.RecordTime;
                            }
                            else //如果是连续进入一个时间段，持续更新最后的结束时间和电量值
                            {
                                endP = item.ElectricPower;
                                etime = item.RecordTime;
                            }
                        }
                    }
                    else if (GenStartSpan3 <= span && span <= GenEndSpan3)
                    {
                        // 且这个时间段为刚刚进入的时间段
                        if (gen3 == false)
                        {
                            gen3 = true;
                            // 把之前保存的数据信息写入到相应的时间段内
                            if (low1 || low2)
                                obj.JHValleyConsumption += endP - beginP;
                            else if (gen1 || gen2)
                                obj.JHAvgConsumption += endP - beginP;
                            else if (peak1 || peak2)
                                obj.JHPeakConsumption += endP - beginP;
                            low1 = false; low2 = false;
                            gen1 = false; gen2 = false;
                            peak1 = false; peak2 = false;
                            // 初始化开始和结束的值
                            beginP = item.ElectricPower;
                            endP = item.ElectricPower;
                            stime = item.RecordTime;
                            etime = item.RecordTime;
                        }
                        else // 如果是连续的进入一个时间段
                        {
                            // 如果间隔大于12小时，则认为是一个新的时间段，需要把以前的时间段内的数据保存起来
                            TimeSpan interval = item.RecordTime - stime;
                            if (interval.TotalHours > 12)
                            {
                                obj.JHAvgConsumption += endP - beginP;
                                beginP = item.ElectricPower;
                                endP = item.ElectricPower;
                                stime = item.RecordTime;
                                etime = item.RecordTime;
                            }
                            else //如果是连续进入一个时间段，持续更新最后的结束时间和电量值
                            {
                                endP = item.ElectricPower;
                                etime = item.RecordTime;
                            }
                        }
                    }
                    else if (PeakStartSpan1 <= span && span <= PeakEndSpan1)
                    {
                        // 且这个时间段为刚刚进入的时间段
                        if (peak1 == false)
                        {
                            peak1 = true;
                            // 把之前保存的数据信息写入到相应的时间段内
                            if (low1 || low2)
                                obj.JHValleyConsumption += endP - beginP;
                            else if (gen1 || gen2 || gen3)
                                obj.JHAvgConsumption += endP - beginP;
                            else if (peak2)
                                obj.JHPeakConsumption += endP - beginP;
                            low1 = false; low2 = false;
                            gen1 = false; gen2 = false; gen3 = false;
                            peak2 = false;
                            // 初始化开始和结束的值
                            beginP = item.ElectricPower;
                            endP = item.ElectricPower;
                            stime = item.RecordTime;
                            etime = item.RecordTime;
                        }
                        else // 如果是连续的进入一个时间段
                        {
                            // 如果间隔大于12小时，则认为是一个新的时间段，需要把以前的时间段内的数据保存起来
                            TimeSpan interval = item.RecordTime - stime;
                            if (interval.TotalHours > 12)
                            {
                                obj.JHPeakConsumption += endP - beginP;
                                beginP = item.ElectricPower;
                                endP = item.ElectricPower;
                                stime = item.RecordTime;
                                etime = item.RecordTime;
                            }
                            else //如果是连续进入一个时间段，持续更新最后的结束时间和电量值
                            {
                                endP = item.ElectricPower;
                                etime = item.RecordTime;
                            }
                        }
                    }
                    else if (PeakStartSpan2 <= span && span <= PeakEndSpan2)
                    {
                        // 且这个时间段为刚刚进入的时间段
                        if (peak2 == false)
                        {
                            peak2 = true;
                            // 把之前保存的数据信息写入到相应的时间段内
                            if (low1 || low2)
                                obj.JHValleyConsumption += endP - beginP;
                            else if (gen1 || gen2 || gen3)
                                obj.JHAvgConsumption += endP - beginP;
                            else if (peak1)
                                obj.JHPeakConsumption += endP - beginP;
                            low1 = false; low2 = false;
                            gen1 = false; gen2 = false; gen3 = false;
                            peak1 = false;
                            // 初始化开始和结束的值
                            beginP = item.ElectricPower;
                            endP = item.ElectricPower;
                            stime = item.RecordTime;
                            etime = item.RecordTime;
                        }
                        else // 如果是连续的进入一个时间段
                        {
                            // 如果间隔大于12小时，则认为是一个新的时间段，需要把以前的时间段内的数据保存起来
                            TimeSpan interval = item.RecordTime - stime;
                            if (interval.TotalHours > 12)
                            {
                                obj.JHPeakConsumption += endP - beginP;
                                beginP = item.ElectricPower;
                                endP = item.ElectricPower;
                                stime = item.RecordTime;
                                etime = item.RecordTime;
                            }
                            else //如果是连续进入一个时间段，持续更新最后的结束时间和电量值
                            {
                                endP = item.ElectricPower;
                                etime = item.RecordTime;
                            }
                        }
                    }
                }
                obj.JHMaxCurrent = maxCurrent == double.MinValue ? 0 : maxCurrent;
                obj.JHMinCurrent = minCurrent == double.MaxValue ? 0 : minCurrent;
                obj.JHMaxLoad = maxPower == double.MinValue ? 0 : maxPower;
                obj.JHPeakConsumption = Math.Round(obj.JHPeakConsumption, 0);
                obj.JHAvgConsumption = Math.Round(obj.JHAvgConsumption, 0);
                obj.JHValleyConsumption = Math.Round(obj.JHValleyConsumption, 0);
                obj.JHElectricCharge = obj.JHPeakConsumption * peakPrice + obj.JHAvgConsumption * genPrice + obj.JHValleyConsumption * lowPrice;
            }
            return obj;
        }

        private void GetJCJMeterDetailInfo(string proline, DateTime startTime, DateTime endTime, float price, ref MeterReadingDetailInfo obj, MeterRanger typeRanger)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                obj.AmmeterID = proline;
                obj.AmmeterPlace = proline + "-" + "挤出机";
                double maxCurrent = double.MinValue;
                double minCurrent = double.MaxValue;
                double maxPower = double.MinValue;
                double beginP = 0;
                double endP = 0;
                bool isFirstLine = true;
                string query = string.Format(GET_JCJPOWER_BY_TIME, proline, startTime, endTime);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        if (isFirstLine)
                        {
                            beginP = double.Parse(reader["ElectricPower"].ToString()) / 10;
                            isFirstLine = false;
                        }
                        double tmpP = double.Parse(reader["ElectricPower"].ToString()) / 10;
                        if (tmpP <= 0 || tmpP < beginP)
                            continue;

                        endP = tmpP;
                        double maxCur = double.Parse(reader["MaxCurrent"].ToString()) / 10;
                        maxCurrent = maxCurrent < maxCur ? maxCur : maxCurrent;
                        double minCur = double.Parse(reader["MinCurrent"].ToString()) / 10;
                        if (minCur >= 0 && minCur < 3402823)
                            minCurrent = minCurrent > minCur ? minCur : minCurrent;
                        double maxPow = double.Parse(reader["MaxPower"].ToString()) / 10;
                        maxPower = maxPower < maxPow ? maxPow : maxPower;
                    }
                    reader.Close();
                }
                obj.JCMaxCurrent = obj.JCMaxCurrent > maxCurrent ? obj.JCMaxCurrent : maxCurrent;
                if (obj.JCMinCurrent == 0 && minCurrent != double.MaxValue)
                    obj.JCMinCurrent = minCurrent;
                else if(minCurrent != double.MaxValue)
                    obj.JCMinCurrent = obj.JCMinCurrent < minCurrent ? obj.JCMinCurrent : minCurrent;
                obj.JCMaxLoad = obj.JCMaxLoad > maxPower ? obj.JCMaxLoad : maxPower;
                obj.MaxCurrent = obj.JCMaxCurrent > obj.MaxCurrent ? obj.JCMaxCurrent : obj.MaxCurrent;
                obj.MaxLoad = obj.JCMaxLoad > obj.MaxLoad ? obj.JCMaxLoad : obj.MaxLoad;
                if (obj.MinCurrent == 0 && obj.JCMinCurrent > 0)
                    obj.MinCurrent = obj.JCMinCurrent;
                else if (obj.JCMinCurrent > 0)
                    obj.MinCurrent = obj.MinCurrent > obj.JCMinCurrent ? obj.JCMinCurrent : obj.MinCurrent;
                double powerTotal = endP - beginP;
                double priceTotal = powerTotal * price;
                obj.JCElectricCharge += priceTotal;
                obj.ElectricCharge += priceTotal;
                switch (typeRanger)
                {
                    case MeterRanger.LOW:
                        obj.JCValleyConsumption += powerTotal;
                        obj.ValleyConsumption += powerTotal;
                        break;
                    case MeterRanger.GEN:
                        obj.JCAvgConsumption += powerTotal;
                        obj.AvgConsumption += powerTotal;
                        break;
                    case MeterRanger.PEAK:
                        obj.JCPeakConsumption += powerTotal;
                        obj.PeakConsumption += powerTotal;
                        break;
                }
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetJCJMeterDetailInfo() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
            }
        }
        private void GetJHJMeterDetailInfo(string proline, DateTime startTime, DateTime endTime, float price, ref MeterReadingDetailInfo obj, MeterRanger typeRanger)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            string query = "";
            try
            {
                //ReConnectSQL();
                obj.AmmeterID = proline;
                obj.AmmeterPlace = proline + "-" + "均化机";
                double maxCurrent = double.MinValue;
                double minCurrent = double.MaxValue;
                double maxPower = double.MinValue;
                double beginP = 0;
                double endP = 0;
                bool isFirstLine = true;
                query = string.Format(GET_JHJPOWER_BY_TIME, proline, startTime, endTime);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        if (isFirstLine)
                        {
                            beginP = double.Parse(reader["ElectricPower"].ToString());
                            isFirstLine = false;
                        }
                        double tmpP = double.Parse(reader["ElectricPower"].ToString());
                        if (tmpP <= 0 || tmpP < beginP)
                            continue;

                        endP = tmpP;
                        double maxCur = double.Parse(reader["MaxCurrent"].ToString());
                        maxCurrent = maxCurrent < maxCur ? maxCur : maxCurrent;
                        double minCur = double.Parse(reader["MinCurrent"].ToString());
                        if (minCur >= 0 && minCur < 3402823)
                            minCurrent = minCurrent > minCur ? minCur : minCurrent;
                        double maxPow = double.Parse(reader["MaxPower"].ToString());
                        maxPower = maxPower < maxPow ? maxPow : maxPower;
                    }
                    reader.Close();
                }
                obj.JHMaxCurrent = obj.JHMaxCurrent > maxCurrent ? obj.JHMaxCurrent : maxCurrent;
                if (obj.JHMinCurrent == 0 && minCurrent != double.MaxValue)
                    obj.JHMinCurrent = minCurrent;
                else if (minCurrent != double.MaxValue)
                    obj.JHMinCurrent = obj.JHMinCurrent < minCurrent ? obj.JHMinCurrent : minCurrent;
                obj.JHMaxLoad = obj.JHMaxLoad > maxPower ? obj.JHMaxLoad : maxPower;
                obj.MaxCurrent = obj.JHMaxCurrent > obj.MaxCurrent ? obj.JHMaxCurrent : obj.MaxCurrent;
                obj.MaxLoad = obj.JHMaxLoad > obj.MaxLoad ? obj.JHMaxLoad : obj.MaxLoad;
                if (obj.MinCurrent == 0 && obj.JHMinCurrent > 0)
                    obj.MinCurrent = obj.JHMinCurrent;
                else if(obj.JHMinCurrent > 0)
                    obj.MinCurrent = obj.MinCurrent > obj.JHMinCurrent ? obj.JHMinCurrent : obj.MinCurrent;
                double powerTotal = endP - beginP;
                double priceTotal = powerTotal * price;
                obj.JHElectricCharge += priceTotal;
                obj.ElectricCharge += priceTotal;
                switch (typeRanger)
                {
                    case MeterRanger.LOW:
                        obj.JHValleyConsumption += powerTotal;
                        obj.ValleyConsumption += powerTotal;
                        break;
                    case MeterRanger.GEN:
                        obj.JHAvgConsumption += powerTotal;
                        obj.AvgConsumption += powerTotal;
                        break;
                    case MeterRanger.PEAK:
                        obj.JHPeakConsumption += powerTotal;
                        obj.PeakConsumption += powerTotal;
                        break;
                }
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetJHJMeterDetailInfo() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Warning(query);
                LogRecord.Error(msg);
                ////ReConnectSQL();
            }
        }
        #endregion

        #region 获得高低柜状态数据
        public List<string> GetHighLowDeviceList()
        {
            //SqlCommand cmd = null;
            //SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                List<string> retList = new List<string>();
                // 方法一：从数据库中取得设备编号列表
                //using (cmd = new SqlCommand(GET_ALLHIGHLOWDEVICE, conn))
                //{
                //    reader = cmd.ExecuteReader();
                //    while(reader.Read())
                //    {
                //        string deviceID = reader["DeviceID"].ToString();
                //        retList.Add(deviceID);
                //    }
                //    reader.Close();
                //}
                // 方法二：从资源文件中取得设备编号列表
                string[] deviceList = PRET.DBAdapter.Properties.Resources.HighLowDeviceDetailList.Split(new char[] { ';' });
                retList = deviceList.ToList();
                return retList;
            }
            catch (Exception ex)
            {
                //if (reader != null && !reader.IsClosed)
                //    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetHighLowDeviceList() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
                return null;
            }
        }

        public List<HighLowStatusReport> GetHighLowStatusReport(string deviceID, DateTime month)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                // 获得这个月内的高低柜数据
                List<HighLowStatusReport> retList = new List<HighLowStatusReport>();
                string strStart = month.ToString("yyyy-MM");
                DateTime startTime = DateTime.Parse(strStart);
                string strEnd = month.AddMonths(1).ToString("yyyy-MM");
                DateTime endTime = DateTime.Parse(strEnd);
                string query = string.Format(GET_HIGHLOWDEVICE_BY_DEVICEID, deviceID, startTime, endTime);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        HighLowStatusReport obj = new HighLowStatusReport();
                        obj.Date = DateTime.Parse(reader["Date"].ToString());
                        obj.MaxCurrent = double.Parse(reader["MaxCurrent"].ToString());
                        obj.MinCurrent = double.Parse(reader["MinCurrent"].ToString());
                        obj.MaxLoad = double.Parse(reader["MaxLoad"].ToString());
                        obj.MinLoad = double.Parse(reader["MinLoad"].ToString());
                        obj.MaxVoltage = double.Parse(reader["MaxVoltage"].ToString());
                        obj.MinVoltage = double.Parse(reader["MinVoltage"].ToString());
                        obj.PowerFactor = double.Parse(reader["PowerFactor"].ToString());
                        retList.Add(obj);
                    }
                    reader.Close();
                }

                // 获得这个月内的温湿度值
                for (int i = 0; i < retList.Count; i++)
                {
                    startTime = retList[i].Date.Date;
                    endTime = retList[i].Date.AddDays(1).Date;
                    query = string.Format(GET_HUMIDITY_TEMPERATURE_BY_TIME, startTime, endTime);
                    conn = new SqlConnection();
                    conn.ConnectionString = this.ConnString;
                    conn.Open();
                    using (cmd = new SqlCommand(query, conn))
                    {
                        cmd.CommandTimeout = 0;
                        reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                        if (reader.Read())
                        {
                            retList[i].MaxHumidity = double.Parse(reader["MaxHumidity"].ToString()) / 10;
                            retList[i].MaxTemperature = double.Parse(reader["MaxTemperature"].ToString()) / 10;
                        }
                        reader.Close();
                    }
                }
                retList.Sort((left, right) =>
                {
                    TimeSpan span = right.Date - left.Date;
                    return (int)span.TotalMinutes;
                });
                return retList;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetHighLowStatusReport() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
                return null;
            }
        }

        public HighLowVoltageReport GetHighLowVoltageReport(DateTime startTime, DateTime endTime)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                // 获得这个月内的高低柜数据
                HighLowVoltageReport retObj = new HighLowVoltageReport();
                // 获得所有设备列表
                string[] deviceList = PRET.DBAdapter.Properties.Resources.HighLowDeviceList.Split(new char[] { ';' });
                foreach (string deviceID in deviceList)
                {
                    float endDegree = 0, startDegree = 0;
                    string query = string.Format(GET_HIGHLOWVOLTAGE_BY_DESC, deviceID, startTime, endTime);
                    SqlConnection conn = new SqlConnection();
                    conn.ConnectionString = this.ConnString;
                    conn.Open();
                    using (cmd = new SqlCommand(query, conn))
                    {
                        cmd.CommandTimeout = 0;
                        reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                        if (reader.Read())
                        {
                            endDegree = float.Parse(reader["Degree"].ToString());
                        }
                        reader.Close();
                    }
                    conn = new SqlConnection();
                    conn.ConnectionString = this.ConnString;
                    conn.Open();
                    query = string.Format(GET_HIGHLOWVOLTAGE_BY_ASC, deviceID, startTime, endTime);
                    using (cmd = new SqlCommand(query, conn))
                    {
                        cmd.CommandTimeout = 0;
                        reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                        if (reader.Read())
                        {
                            startDegree = float.Parse(reader["Degree"].ToString());
                        }
                        reader.Close();
                    }
                    #region degree
                    switch (deviceID)
                    {
                        case "1D2_1":
                            retObj.D1D2_1 = endDegree - startDegree;
                            break;
                        case "1D2_2":
                            retObj.D1D2_2 = endDegree - startDegree;
                            break;
                        case "1D3":
                            retObj.D1D3 = endDegree - startDegree;
                            break;
                        case "1D4":
                            retObj.D1D4 = endDegree - startDegree;
                            break;
                        case "1D5_1":
                            retObj.D1D5_1 = endDegree - startDegree;
                            break;
                        case "1D5_2":
                            retObj.D1D5_2 = endDegree - startDegree;
                            break;
                        case "1D6_1":
                            retObj.D1D6_1 = endDegree - startDegree;
                            break;
                        case "1D6_2":
                            retObj.D1D6_2 = endDegree - startDegree;
                            break;
                        case "2D2":
                            retObj.D2D2 = endDegree - startDegree;
                            break;
                        case "2D3":
                            retObj.D2D3 = endDegree - startDegree;
                            break;
                        case "2D4_1":
                            retObj.D2D4_1 = endDegree - startDegree;
                            break;
                        case "2D4_2":
                            retObj.D2D4_2 = endDegree - startDegree;
                            break;
                        case "2D5_1":
                            retObj.D2D5_1 = endDegree - startDegree;
                            break;
                        case "2D5_2":
                            retObj.D2D5_2 = endDegree - startDegree;
                            break;
                        case "2D6":
                            retObj.D2D6 = endDegree - startDegree;
                            break;
                        case "3D2":
                            retObj.D3D2 = endDegree - startDegree;
                            break;
                        case "3D3":
                            retObj.D3D3 = endDegree - startDegree;
                            break;
                        case "3D4":
                            retObj.D3D4 = endDegree - startDegree;
                            break;
                        case "3D5":
                            retObj.D3D5 = endDegree - startDegree;
                            break;
                        case "3D6_1":
                            retObj.D3D6_1 = endDegree - startDegree;
                            break;
                        case "3D6_2":
                            retObj.D3D6_2 = endDegree - startDegree;
                            break;
                        case "3D7_1":
                            retObj.D3D7_1 = endDegree - startDegree;
                            break;
                        case "3D7_2":
                            retObj.D3D7_2 = endDegree - startDegree;
                            break;
                        case "3D7_3":
                            retObj.D3D7_3 = endDegree - startDegree;
                            break;
                        case "3D8_1":
                            retObj.D3D8_1 = endDegree - startDegree;
                            break;
                        case "3D8_2":
                            retObj.D3D8_2 = endDegree - startDegree;
                            break;
                        case "3D8_3":
                            retObj.D3D8_3 = endDegree - startDegree;
                            break;
                        case "3D8_4":
                            retObj.D3D8_4 = endDegree - startDegree;
                            break;
                        case "3D8_5":
                            retObj.D3D8_5 = endDegree - startDegree;
                            break;
                        case "3D8_6":
                            retObj.D3D8_6 = endDegree - startDegree;
                            break;
                        case "3D8_7":
                            retObj.D3D8_7 = endDegree - startDegree;
                            break;
                        case "3D8_8":
                            retObj.D3D8_8 = endDegree - startDegree;
                            break;
                        case "3D9_1":
                            retObj.D3D9_1 = endDegree - startDegree;
                            break;
                        case "3D9_2":
                            retObj.D3D9_2 = endDegree - startDegree;
                            break;
                        case "3D9_3":
                            retObj.D3D9_3 = endDegree - startDegree;
                            break;
                        case "3D9_4":
                            retObj.D3D9_4 = endDegree - startDegree;
                            break;
                        case "3D9_5":
                            retObj.D3D9_5 = endDegree - startDegree;
                            break;
                        case "3D9_6":
                            retObj.D3D9_6 = endDegree - startDegree;
                            break;
                        case "3D9_7":
                            retObj.D3D9_7 = endDegree - startDegree;
                            break;
                        case "3D9_8":
                            retObj.D3D9_8 = endDegree - startDegree;
                            break;
                        case "3D9_9":
                            retObj.D3D9_9 = endDegree - startDegree;
                            break;
                        case "3D10_1":
                            retObj.D3D10_1 = endDegree - startDegree;
                            break;
                        case "3D10_2":
                            retObj.D3D10_2 = endDegree - startDegree;
                            break;
                        case "3D10_3":
                            retObj.D3D10_3 = endDegree - startDegree;
                            break;
                        case "3D10_4":
                            retObj.D3D10_4 = endDegree - startDegree;
                            break;
                        case "3D10_5":
                            retObj.D3D10_5 = endDegree - startDegree;
                            break;
                        case "3D10_6":
                            retObj.D3D10_6 = endDegree - startDegree;
                            break;
                        case "3D10_7":
                            retObj.D3D10_7 = endDegree - startDegree;
                            break;
                        case "3D10_8":
                            retObj.D3D10_8 = endDegree - startDegree;
                            break;
                        case "3D10_9":
                            retObj.D3D10_9 = endDegree - startDegree;
                            break;
                        case "3D11_1":
                            retObj.D3D11_1 = endDegree - startDegree;
                            break;
                        case "3D11_2":
                            retObj.D3D11_2 = endDegree - startDegree;
                            break;
                        case "3D11_3":
                            retObj.D3D11_3 = endDegree - startDegree;
                            break;
                        case "3D11_4":
                            retObj.D3D11_4 = endDegree - startDegree;
                            break;
                        case "3D11_5":
                            retObj.D3D11_5 = endDegree - startDegree;
                            break;
                        case "3D11_6":
                            retObj.D3D11_6 = endDegree - startDegree;
                            break;
                        case "3D12_1":
                            retObj.D3D12_1 = endDegree - startDegree;
                            break;
                        case "3D12_2":
                            retObj.D3D12_2 = endDegree - startDegree;
                            break;
                        case "3D12_3":
                            retObj.D3D12_3 = endDegree - startDegree;
                            break;
                        case "3D12_4":
                            retObj.D3D12_4 = endDegree - startDegree;
                            break;
                        case "1#_meter":
                            retObj.D1MeterDegree = endDegree - startDegree;
                            break;
                        case "2#_meter":
                            retObj.D2MeterDegree = endDegree - startDegree;
                            break;
                        case "3#_meter":
                            retObj.D3MeterDegree = endDegree - startDegree;
                            break;
                    }
                    #endregion
                }
                return retObj;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetHighLowStatusReport() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
                return null;
            }
        }
        #endregion

        #region 获得生产任务数据
        public List<ProductionReportSummaryForJSON> GetProLineStatus()
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                ////ReConnectSQL();
                List<ProductionReportSummaryForJSON> retList = new List<ProductionReportSummaryForJSON>();
                // 获得指定年内的所有生产线
                List<string> productionLines = this.GetAllProductionLineNames();
                foreach (string proLine in productionLines)
                {
                    ProductionReportSummaryForJSON pe = new ProductionReportSummaryForJSON();
                    pe.ProductionLine = proLine;
                    pe.OrderID = "";
                    pe.ProductName = "";
                    pe.OrderFinished = true;
                    pe.ProLineStop = true;
                    pe.ExtruderName = "";
                    pe.PackerName = "";
                    DateTime updateTime = DateTime.Now;
                    string query = string.Format(GET_JCJORDER_BY_UPDATETIME, proLine);
                    SqlConnection conn = new SqlConnection();
                    conn.ConnectionString = this.ConnString;
                    conn.Open();
                    using (cmd = new SqlCommand(query, conn))
                    {
                        cmd.CommandTimeout = 0;
                        reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                        if (reader.Read())
                        {
                            //pe.OrderID = reader["OrderID"].ToString();
                            // 如果EndTime为NULL,说明当前订单没有结束
                            if (string.IsNullOrEmpty(reader["EndTime"].ToString()))
                            {
                                pe.OrderID = reader["OrderID"].ToString();
                                pe.OrderFinished = false;
                                pe.ProLineStop = false;
                                updateTime = DateTime.Parse(reader["UpdateTime"].ToString());
                            }
                            //updateTime = DateTime.Parse(reader["UpdateTime"].ToString());
                        }
                        reader.Close();
                    }
                    if (string.IsNullOrEmpty(pe.OrderID) || pe.OrderID == "MO0000000000")
                    {
                        pe.OrderID = "";
                    }
                    else
                    {
                        ERPProductionInfo info = GetProductionInfo(pe.OrderID, proLine);
                        if (info != null)
                        {
                            pe.OrderID = info.OrderID;
                            pe.ProductName = info.ProductName;
                        }
                        // 获得人员信息
                        GetMemberInfo(proLine, updateTime, ref pe);
                    }
                    retList.Add(pe);
                }
                return retList;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetProLineStatus() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
                return null;
            }
        }
        private void GetMemberInfo(string proline, DateTime workTime, ref ProductionReportSummaryForJSON obj)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                // 1. 获得这个时间段内的员工ID
                string query = string.Format(GET_MEMBERID_BY_TIME, proline, workTime);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    if (reader.Read())
                    {
                        obj.TeamName = reader["TeamID"].ToString();
                        obj.MixingerName = reader["MixingerID"].ToString();
                        obj.ExtruderName = reader["ExtruderID"].ToString();
                        obj.PackerName = reader["PackerID"].ToString();
                        obj.QualityName = reader["QualityID"].ToString();
                    }
                    reader.Close();
                }
                if (!string.IsNullOrEmpty(obj.MixingerName))
                    obj.MixingerName = GetEmployeeName(obj.MixingerName);
                if (!string.IsNullOrEmpty(obj.ExtruderName))
                    obj.ExtruderName = GetEmployeeName(obj.ExtruderName);
                if (!string.IsNullOrEmpty(obj.PackerName))
                    obj.PackerName = GetEmployeeName(obj.PackerName);
                if (!string.IsNullOrEmpty(obj.QualityName))
                    obj.QualityName = GetEmployeeName(obj.QualityName);
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetMemberInfo() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
            }
        }

        public List<ProductiveTaskReportTV> GetProductiveTaskReportForTV()
        {
        //    SqlCommand cmd = null;
        //    SqlDataReader reader = null;
        //    try
        //    {
        //        ////ReConnectSQL();
        //        List<ProductiveTaskReportTV> retList = new List<ProductiveTaskReportTV>();
        //        // 1. 获得当前所有生产线
        //        List<string> productionLines = this.GetAllProductionLineNames();
        //        // 2. 获得生产线上的在产信息
        //        foreach (string proLine in productionLines)
        //        {
        //            // 获得这个生产线的生产效率
        //            ProductiveTaskReportTV task = new ProductiveTaskReportTV();
        //            task.ProductLine = proLine;
        //            double weight;
        //            double efficiency = GetProLineEfficiency(proLine, out weight);
        //            OrderTask working = GetWorkingOrder(proLine, efficiency);
        //            if (working != null)
        //            {
        //                task.ProductionEfficiency = Math.Round(efficiency, 0);
        //                task.Weight = weight;
        //                task.StartTime = working.StartTime;
        //                task.CurrentOrderID = working.OrderID;
        //                task.CurrentProductName = working.ProductionName;
        //                task.CurrentWeight = working.Weight;
        //                task.CurrentEstimateEndTime = working.EstimateEndTime;
        //                DateTime updateTime = DateTime.Now;
        //                string orderid = working.OrderID;
        //                if (orderid.Length > 12)
        //                    orderid = orderid.Substring(2);
        //                GetMemberInfo(proLine, working.UpdateTime, ref task);

        //                string query = string.Format(GET_JCJPOWER_BY_TIMERANGE, proLine, working.StartTime, working.UpdateTime);
        //                SqlConnection conn = new SqlConnection();
        //                conn.ConnectionString = this.ConnString;
        //                conn.Open();
        //                using (cmd = new SqlCommand(query, conn))
        //                {
        //                    double beginP = 0, endP = 0;
        //                    bool isFirstLine = true;
        //                    cmd.CommandTimeout = 0;
        //                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        //                    while (reader.Read())
        //                    {
        //                        if (isFirstLine)
        //                        {
        //                            endP = double.Parse(reader["ElectricPower"].ToString()) / 10;
        //                            if (endP > 0)
        //                                isFirstLine = false;
        //                        }
        //                        else
        //                        {
        //                            beginP = double.Parse(reader["ElectricPower"].ToString()) / 10;
        //                        }
        //                    }
        //                    task.TotalPower = Math.Round(endP - beginP, 0);
        //                    if (task.Weight > 0)
        //                        task.TotalEnergy = Math.Round(task.TotalPower / task.Weight, 2);
        //                    reader.Close();
        //                }
        //            }
        //            retList.Add(task);
        //        }
        //        return retList;
        //    }
        //    catch (Exception ex)
        //    {
        //        if (reader != null && !reader.IsClosed)
        //            reader.Close();
        //        //if (cmd != null)
        //            //cmd.Dispose();
        //        string msg = string.Format("SQLDataProcess.GetProductiveTaskReportForTV() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
        //        LogRecord.Error(msg);
        //        ////ReConnectSQL();
        //        return null;
        //    }

            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                ////ReConnectSQL();
                List<ProductiveTaskReportTV> retList = new List<ProductiveTaskReportTV>();
                string query = string.Format(GET_ALL_PRODUCE_TASK);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        ProductiveTaskReportTV obj = new ProductiveTaskReportTV();
                        obj.ProductLine = reader["ProductLine"].ToString();
                        obj.CurrentProductName = reader["CurrentProductName"].ToString();
                        obj.CurrentWeight = double.Parse(reader["CurrentWeight"].ToString());
                        if (!string.IsNullOrEmpty(reader["StartTime"].ToString()))
                        {
                            obj.StartTime = DateTime.Parse(reader["StartTime"].ToString());
                        }
                        if (!string.IsNullOrEmpty(reader["CurrentEstimateEndTime"].ToString()))
                        {
                            obj.CurrentEstimateEndTime = DateTime.Parse(reader["CurrentEstimateEndTime"].ToString());
                        }
                        obj.CurrentOrderID = reader["CurrentOrderID"].ToString();
                        obj.TeamName = reader["TeamName"].ToString();
                        obj.MixingerName = reader["MixingerName"].ToString();
                        obj.ExtruderName = reader["ExtruderName"].ToString();
                        obj.PackerName = reader["PackerName"].ToString();
                        obj.QualityName = reader["QualityName"].ToString();
                        obj.ProductionEfficiency = double.Parse(reader["ProductionEfficiency"].ToString());
                        obj.Weight = double.Parse(reader["Weight"].ToString());
                        obj.TotalPower = double.Parse(reader["TotalPower"].ToString());
                        obj.TotalEnergy = double.Parse(reader["TotalEnergy"].ToString());
                        retList.Add(obj);
                    }
                    reader.Close();
                }
                retList.Sort((left, right) =>
                {
                    int proline1 = int.Parse(left.ProductLine.Substring(1));
                    int proline2 = int.Parse(right.ProductLine.Substring(1));
                    return proline1 - proline2;
                });
                return retList;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetProductiveTaskReportForTV() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
                return null;
            }
        }
        private void GetMemberInfo(string proline, DateTime workTime, ref ProductiveTaskReportTV obj)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                // 1. 获得这个时间段内的员工ID
                string query = string.Format(GET_MEMBERID_BY_TIME, proline, workTime);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    if (reader.Read())
                    {
                        obj.TeamName = reader["TeamID"].ToString();
                        obj.MixingerName = reader["MixingerID"].ToString();
                        obj.ExtruderName = reader["ExtruderID"].ToString();
                        obj.PackerName = reader["PackerID"].ToString();
                        obj.QualityName = reader["QualityID"].ToString();
                    }
                    reader.Close();
                }
                if (!string.IsNullOrEmpty(obj.MixingerName))
                    obj.MixingerName = GetEmployeeName(obj.MixingerName);
                if (!string.IsNullOrEmpty(obj.ExtruderName))
                    obj.ExtruderName = GetEmployeeName(obj.ExtruderName);
                if (!string.IsNullOrEmpty(obj.PackerName))
                    obj.PackerName = GetEmployeeName(obj.PackerName);
                if (!string.IsNullOrEmpty(obj.QualityName))
                    obj.QualityName = GetEmployeeName(obj.QualityName);
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetMemberInfo() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
            }
        }

        public List<ProductiveTaskReport> GetProductiveTaskReport()
        {
            List<ProductiveTaskReport> retList = new List<ProductiveTaskReport>();
            // 1. 获得当前所有生产线
            //List<string> productionLines = GetAllProductionLineName();
            // 获得指定年内的所有生产线
            List<string> productionLines = this.GetAllProductionLineNames();

            // 2. 获得生产线上的在产信息和待产信息
            foreach (string proLine in productionLines)
            {
                // 获得这个生产线的生产效率
                double weight;
                double efficiency = GetProLineEfficiency(proLine, out weight);
                OrderTask waiting = GetWaitingOrder(proLine);
                if (waiting != null)
                {
                    List<string> allOrder = GetAllOrderList(proLine);
                    foreach (string item in allOrder)
                    {
                        if (waiting.OrderID == item)
                        {
                            waiting = null;
                            break;
                        }
                    }
                }

                OrderTask working = GetWorkingOrder(proLine, efficiency);
                ProductiveTaskReport task = null;
                if (working != null)
                {
                    task = new ProductiveTaskReport();
                    task.ProductLine = proLine;
                    task.CurrentOrderID = working.OrderID;
                    task.CurrentProductName = working.ProductionName;
                    task.CurrentWeight = working.Weight;
                    task.CurrentEstimateEndTime = working.EstimateEndTime;
                }
                if (waiting != null)
                {
                    if (task == null)
                    {
                        task = new ProductiveTaskReport();
                        task.ProductLine = proLine;
                    }
                    task.NextOrderID = waiting.OrderID;
                    task.NextProductName = waiting.ProductionName;
                    task.NextWeight = waiting.Weight;
                    if (task.CurrentEstimateEndTime != null)
                        task.NextEstimateStartTime = ((DateTime)task.CurrentEstimateEndTime).AddMinutes(30);
                    else
                    {
                        task.NextEstimateStartTime = null;
                    }
                    if (efficiency != 0 && task.NextEstimateStartTime != null)
                    {
                        double hours = task.NextWeight / efficiency;
                        task.NextEstimateEndTime = ((DateTime)task.NextEstimateStartTime).AddHours(hours);
                    }
                    else
                        task.NextEstimateEndTime = null;
                }
                if (task != null)
                    retList.Add(task);
            }
            return retList;
        }
        private List<string> GetAllProductionLineName()
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                List<string> retList = new List<string>();
                HashSet<string> hashPro = new HashSet<string>();
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(GET_ALLJCJPRODUCTIONLINE_FROM_SYS, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        string proLine = reader["ProductionLine"].ToString();
                        retList.Add(proLine);
                        //if(!hashPro.Contains(proLine))
                        //{
                        //    hashPro.Add(proLine);
                        //    retList.Add(proLine);
                        //}
                    }
                    reader.Close();
                }

                //using (SqlCommand cmd = new SqlCommand(GET_ALLJHJPRODUCTIONLINE_FROM_SYS, conn))
                //{
                //    SqlDataReader reader = cmd.ExecuteReader();
                //    while (reader.Read())
                //    {
                //        string proLine = reader["ProductionLine"].ToString();
                //        if (!hashPro.Contains(proLine))
                //        {
                //            hashPro.Add(proLine);
                //            retList.Add(proLine);
                //        }
                //    }
                //    reader.Close();
                //}
                return retList;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetAllProductionLineName() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
                return null;
            }
        }
        private OrderTask GetWorkingOrder(string proLine, double efficiency)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                string orderID = "";
                DateTime? startTime = null;
                DateTime updateTime = DateTime.Now;
                // 已用时间
                string query = string.Format(GET_JCJORDER_BY_UPDATETIME, proLine);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    if (reader.Read())
                    {
                        orderID = reader["OrderID"].ToString();
                        // 如果EndTime为NULL,说明当前订单没有结束
                        if (!string.IsNullOrEmpty(reader["EndTime"].ToString()))
                            orderID = "";
                        startTime = DateTime.Parse(reader["StartTime"].ToString());
                        updateTime = DateTime.Parse(reader["UpdateTime"].ToString());
                    }
                    reader.Close();
                }
                if (string.IsNullOrEmpty(orderID) || orderID == "MO0000000000")
                    return null;

                OrderTask order = new OrderTask();
                order.OrderID = orderID;
                order.StartTime = (DateTime)startTime;
                order.UpdateTime = updateTime;
                ERPProductionInfo proInfo = GetProductionInfo(orderID);
                if (proInfo != null)
                {
                    order.OrderID = proInfo.OrderID;
                    order.ProductionName = proInfo.ProductName;
                    order.Weight = proInfo.Count;
                }
                if (efficiency != 0 && order.Weight != 0)
                {
                    double totalHours = order.Weight / efficiency;
                    order.EstimateEndTime = ((DateTime)startTime).AddHours(totalHours);
                }
                return order;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetWorkingOrder() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
                return null;
            }
        }
        private OrderTask GetWaitingOrder(string proLine)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                string orderID = "";
                double weight = 0;
                // 从WaitOrder表格中获得待产的订单号
                string query = string.Format(GET_WAITORDER_BY_PROLINE, proLine);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    if (reader.Read())
                    {
                        orderID = reader["OrderID"].ToString();
                        weight = double.Parse(reader["Weight"].ToString());
                    }
                    reader.Close();
                }
                if (string.IsNullOrEmpty(orderID) || weight == 0)
                    return null;

                OrderTask order = new OrderTask();
                order.OrderID = orderID;
                order.Weight = weight;
                ERPProductionInfo proInfo = GetProductionInfo(orderID, proLine);
                if (proInfo != null)
                    order.ProductionName = proInfo.ProductName;
                return order;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetWaitingOrder() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
                return null;
            }
        }
        private List<string> GetAllOrderList(string proLine)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                List<string> orderList = new List<string>();
                // 从WaitOrder表格中获得待产的订单号
                string query = string.Format(GET_ALLORDER_FROMJCJ, proLine);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        orderList.Add(reader["OrderID"].ToString());
                    }
                    reader.Close();
                }
                return orderList;
            }
            catch (SqlException ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetAllOrderList() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
                return null;
            }
        }
        private double GetProLineEfficiency(string proLine, out double weight)
        {
            weight = 0;
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                // 获得最近一次订单的生产时间
                DateTime? startTime = null;
                DateTime? endTime = null;
                double efficiency = 0;
                double w1 = 0, w2 = 0, w3 = 0;
                string query = string.Format(GET_JCJDETAIL_BY_PROLINE, proLine);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    bool isStart = false;
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        double tmpw1 = double.Parse(reader["Bal1RealCount"].ToString());
                        double tmpw2 = double.Parse(reader["Bal2RealCount"].ToString());
                        double tmpw3 = double.Parse(reader["Bal3RealCount"].ToString());
                        if (isStart == false && (tmpw1 > 0 || tmpw2 > 0 || tmpw3 > 0))
                        {
                            startTime = DateTime.Parse(reader["RecordTime"].ToString());
                            w1 = tmpw1;
                            w2 = tmpw2;
                            w3 = tmpw3;
                            weight = w1 + w2 + w3;
                            isStart = true;
                        }
                        if (isStart == true)
                        {
                            if (w1 < tmpw1 || w2 < tmpw2 || w3 < tmpw3)
                                break;
                            else
                            {
                                endTime = DateTime.Parse(reader["RecordTime"].ToString());
                                if (tmpw1 == 0 && tmpw2 == 0 && tmpw3 == 0)
                                    break;
                                else
                                {
                                    w1 = tmpw1;
                                    w2 = tmpw2;
                                    w3 = tmpw3;
                                }
                            }
                        }
                    }
                    reader.Close();
                }
                weight -= (w1 + w2 + w3);
                if (weight > 0 && startTime != null && endTime != null)
                {
                    TimeSpan span = (TimeSpan)((DateTime)startTime - (DateTime)endTime);
                    if (span.TotalHours > 0.001)
                        efficiency = weight / span.TotalHours;
                }
                return efficiency;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetProLineEfficiency() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
                return 0;
            }
        }
        #endregion

        #region 获得生产线效率数据
        public List<ProductionLineEfficiencyReport> GetProductionLineEfficiencyReportForTV(DateTime year)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                ////ReConnectSQL();
                List<ProductionLineEfficiencyReport> retList = new List<ProductionLineEfficiencyReport>();
                string query = string.Format(GET_PROLINEEFF, year.Year);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        ProductionLineEfficiencyReport obj = new ProductionLineEfficiencyReport();
                        obj.ProductionLine = reader["ProductionLine"].ToString();
                        obj.JanEfficiency = double.Parse(reader["JanEfficiency"].ToString());
                        obj.JanHours = double.Parse(reader["JanHours"].ToString());
                        obj.JanWeight = double.Parse(reader["JanWeight"].ToString());
                        obj.FebEfficiency = double.Parse(reader["FebEfficiency"].ToString());
                        obj.FebHours = double.Parse(reader["FebHours"].ToString());
                        obj.FebWeight = double.Parse(reader["FebWeight"].ToString());
                        obj.MarEfficiency = double.Parse(reader["MarEfficiency"].ToString());
                        obj.MarHours = double.Parse(reader["MarHours"].ToString());
                        obj.MarWeight = double.Parse(reader["MarWeight"].ToString());
                        obj.AprEfficiency = double.Parse(reader["AprEfficiency"].ToString());
                        obj.AprHours = double.Parse(reader["AprHours"].ToString());
                        obj.AprWeight = double.Parse(reader["AprWeight"].ToString());
                        obj.MayEfficiency = double.Parse(reader["MayEfficiency"].ToString());
                        obj.MayHours = double.Parse(reader["MayHours"].ToString());
                        obj.MayWeight = double.Parse(reader["MayWeight"].ToString());
                        obj.JunEfficiency = double.Parse(reader["JunEfficiency"].ToString());
                        obj.JunHours = double.Parse(reader["JunHours"].ToString());
                        obj.JunWeight = double.Parse(reader["JunWeight"].ToString());
                        obj.JulEfficiency = double.Parse(reader["JulEfficiency"].ToString());
                        obj.JulHours = double.Parse(reader["JulHours"].ToString());
                        obj.JulWeight = double.Parse(reader["JulWeight"].ToString());
                        obj.AugEfficiency = double.Parse(reader["AugEfficiency"].ToString());
                        obj.AugHours = double.Parse(reader["AugHours"].ToString());
                        obj.AugWeight = double.Parse(reader["AugWeight"].ToString());
                        obj.SepEfficiency = double.Parse(reader["SepEfficiency"].ToString());
                        obj.SepHours = double.Parse(reader["SepHours"].ToString());
                        obj.SepWeight = double.Parse(reader["SepWeight"].ToString());
                        obj.OctEfficiency = double.Parse(reader["OctEfficiency"].ToString());
                        obj.OctHours = double.Parse(reader["OctHours"].ToString());
                        obj.OctWeight = double.Parse(reader["OctWeight"].ToString());
                        obj.NovEfficiency = double.Parse(reader["NovEfficiency"].ToString());
                        obj.NovHours = double.Parse(reader["NovHours"].ToString());
                        obj.NovWeight = double.Parse(reader["NovWeight"].ToString());
                        obj.DecEfficiency = double.Parse(reader["DecEfficiency"].ToString());
                        obj.DecHours = double.Parse(reader["DecHours"].ToString());
                        obj.DecWeight = double.Parse(reader["DecWeight"].ToString());

                        obj.AvgEfficiency = double.Parse(reader["AvgEfficiency"].ToString());
                        obj.MaxEfficiency = double.Parse(reader["MaxEfficiency"].ToString());
                        obj.MinEfficiency = double.Parse(reader["MinEfficiency"].ToString());
                        obj.EfficiencyQuota = double.Parse(reader["EfficiencyQuota"].ToString());
                        obj.Weight = double.Parse(reader["Weight"].ToString());
                        obj.Hours = double.Parse(reader["Hours"].ToString());
                        obj.CurrEfficiency = double.Parse(reader["CurrEfficiency"].ToString());
                        retList.Add(obj);
                    }
                    reader.Close();
                }
                retList.Sort((left, right) =>
                {
                    int proline1 = int.Parse(left.ProductionLine.Substring(1));
                    int proline2 = int.Parse(right.ProductionLine.Substring(1));
                    return proline2 - proline1;
                });
                return retList;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetProductionLineEfficiencyReportForTV() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
                return null;
            }
        }

        public List<ProductionLineEfficiencyReport> GetProductionLineEfficiencyReport2(DateTime year, Dictionary<string, int> quota = null)
        {
            try
            {
                ////ReConnectSQL();
                List<ProductionLineEfficiencyReport> retList = new List<ProductionLineEfficiencyReport>();
                // 1. 取出指定年内所有生产线的订单号
                List<string> proList = GetAllProductionLineNames();
                foreach (string proline in proList)
                {
                    ProductionLineEfficiencyReport obj = GetProductionEfficiency(proline, year);
                    ProductionLineStandby ps = GetProductionLineStandbyByYear(proline, year);
                    if (obj == null || ps == null)
                        continue;
                    MergerProductionLineEfficiency(ref obj, ps);
                    if (obj != null)
                    {
                        if (quota != null)
                            obj.EfficiencyQuota = quota[obj.ProductionLine];
                        retList.Add(obj);
                    }
                }

                // 保存效率指标到DB中
                if (quota != null)
                    SaveEfficiencyQuota(year, quota, 1);
                return retList;
            }
            catch (Exception ex)
            {
                string msg = string.Format("SQLDataProcess.GetProductionLineEfficiencyReport2() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
                return null;
            }
        }
        private void MergerProductionLineEfficiency(ref ProductionLineEfficiencyReport obj, ProductionLineStandby ps)
        {
            obj.JanHours += ps.JanTime;
            obj.JanHours = Math.Round(obj.JanHours, 0);
            if (obj.JanHours > 0)
                obj.JanEfficiency = Math.Round(obj.JanWeight / obj.JanHours, 0);
            obj.FebHours += ps.FebTime;
            obj.FebHours = Math.Round(obj.FebHours, 0);
            if (obj.FebHours > 0)
                obj.FebEfficiency = Math.Round(obj.FebWeight / obj.FebHours, 0);
            obj.MarHours += ps.MarTime;
            obj.MarHours = Math.Round(obj.MarHours, 0);
            if (obj.MarHours > 0)
                obj.MarEfficiency = Math.Round(obj.MarWeight / obj.MarHours, 0);
            obj.AprHours += ps.AprTime;
            obj.AprHours = Math.Round(obj.AprHours, 0);
            if (obj.AprHours > 0)
                obj.AprEfficiency = Math.Round(obj.AprWeight / obj.AprHours, 0);
            obj.MayHours += ps.MayTime;
            obj.MayHours = Math.Round(obj.MayHours, 0);
            if (obj.MayHours > 0)
                obj.MayEfficiency = Math.Round(obj.MayWeight / obj.MayHours, 0);
            obj.JunHours += ps.JunTime;
            obj.JunHours = Math.Round(obj.JunHours, 0);
            if (obj.JunHours > 0)
                obj.JunEfficiency = Math.Round(obj.JunWeight / obj.JunHours, 0);
            obj.JulHours += ps.JulTime;
            obj.JulHours = Math.Round(obj.JulHours, 0);
            if (obj.JulHours > 0)
                obj.JulEfficiency = Math.Round(obj.JulWeight / obj.JulHours, 0);
            obj.AugHours += ps.AugTime;
            obj.AugHours = Math.Round(obj.AugHours, 0);
            if (obj.AugHours > 0)
                obj.AugEfficiency = Math.Round(obj.AugWeight / obj.AugHours, 0);
            obj.SepHours += ps.SepTime;
            obj.SepHours = Math.Round(obj.SepHours, 0);
            if (obj.SepHours > 0)
                obj.SepEfficiency = Math.Round(obj.SepWeight / obj.SepHours, 0);
            obj.OctHours += ps.OctTime;
            obj.OctHours = Math.Round(obj.OctHours, 0);
            if (obj.OctHours > 0)
                obj.OctEfficiency = Math.Round(obj.OctWeight / obj.OctHours, 0);
            obj.NovHours += ps.NovTime;
            obj.NovHours = Math.Round(obj.NovHours, 0);
            if (obj.NovHours > 0)
                obj.NovEfficiency = Math.Round(obj.NovWeight / obj.NovHours, 0);
            obj.DecHours += ps.DecTime;
            obj.DecHours = Math.Round(obj.DecHours, 0);
            if (obj.DecHours > 0)
                obj.DecEfficiency = Math.Round(obj.DecWeight / obj.DecHours, 0);
        }
        private ProductionLineEfficiencyReport GetProductionEfficiency(string proline, DateTime year)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                double maxEffic = double.MinValue;
                double minEffic = double.MaxValue;
                ProductionLineEfficiencyReport obj = new ProductionLineEfficiencyReport();
                obj.ProductionLine = proline;
                for (int i = 0; i < 12; i++)
                {
                    string month = String.Format("{0}/{1}/01", year.Year, i + 1);
                    DateTime startM = DateTime.Parse(month);
                    DateTime endM = startM.AddMonths(1);
                    string query = string.Format(GET_PROLINESTATUS_BY_PROLINE, proline, startM, endM);
                    double output = 0, hours = 0, efficiency = 0;
                    SqlConnection conn = new SqlConnection();
                    conn.ConnectionString = this.ConnString;
                    conn.Open();
                    using (cmd = new SqlCommand(query, conn))
                    {
                        cmd.CommandTimeout = 0;
                        reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                        if (reader.Read())
                        {
                            if (reader["Weight"] != null)
                                output = double.Parse(reader["Weight"].ToString());
                            if (reader["TotalTime"] != null)
                                hours = Math.Round(double.Parse(reader["TotalTime"].ToString()) / 60, 0);
                        }
                        reader.Close();
                    }
                    obj.Hours += hours;
                    obj.Weight += output;
                    if (hours > 0)
                    {
                        efficiency = Math.Round(output / hours, 0);
                        maxEffic = maxEffic < efficiency ? efficiency : maxEffic;
                        if (efficiency > 0)
                            minEffic = minEffic > efficiency ? efficiency : minEffic;
                    }
                    switch (i)
                    {
                        case 0:
                            obj.JanEfficiency = efficiency;
                            obj.JanHours = hours;
                            obj.JanWeight = output;
                            break;
                        case 1:
                            obj.FebEfficiency = efficiency;
                            obj.FebHours = hours;
                            obj.FebWeight = output;
                            break;
                        case 2:
                            obj.MarEfficiency = efficiency;
                            obj.MarHours = hours;
                            obj.MarWeight = output;
                            break;
                        case 3:
                            obj.AprEfficiency = efficiency;
                            obj.AprHours = hours;
                            obj.AprWeight = output;
                            break;
                        case 4:
                            obj.MayEfficiency = efficiency;
                            obj.MayHours = hours;
                            obj.MayWeight = output;
                            break;
                        case 5:
                            obj.JunEfficiency = efficiency;
                            obj.JunHours = hours;
                            obj.JunWeight = output;
                            break;
                        case 6:
                            obj.JulEfficiency = efficiency;
                            obj.JulHours = hours;
                            obj.JulWeight = output;
                            break;
                        case 7:
                            obj.AugEfficiency = efficiency;
                            obj.AugHours = hours;
                            obj.AugWeight = output;
                            break;
                        case 8:
                            obj.SepEfficiency = efficiency;
                            obj.SepHours = hours;
                            obj.SepWeight = output;
                            break;
                        case 9:
                            obj.OctEfficiency = efficiency;
                            obj.OctHours = hours;
                            obj.OctWeight = output;
                            break;
                        case 10:
                            obj.NovEfficiency = efficiency;
                            obj.NovHours = hours;
                            obj.NovWeight = output;
                            break;
                        case 11:
                            obj.DecEfficiency = efficiency;
                            obj.DecHours = hours;
                            obj.DecWeight = output;
                            break;
                    }
                }
                if (maxEffic != double.MinValue)
                    obj.MaxEfficiency = Math.Round(maxEffic, 0);
                if (minEffic != double.MaxValue)
                    obj.MinEfficiency = Math.Round(minEffic, 0);
                if (obj.Hours > 0)
                    obj.AvgEfficiency = Math.Round(obj.Weight / obj.Hours, 0);
                return obj;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetProductionEfficiency() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
                return null;
            }
        }

        public List<ProductionLineEfficiencyReport> GetProductionLineEfficiencyReport(DateTime year, Dictionary<string, int> quota = null)
        {
            List<ProductionLineEfficiencyReport> retList = new List<ProductionLineEfficiencyReport>();
            // 1. 取出指定年内所有生产线的订单号
            List<ProductionEfficiency> productionLineData = GetAllProductionLineOrderList(year);
            if (productionLineData != null)
            {
                // 2. 将所有订单号转换成生产线效率数据
                foreach (ProductionEfficiency item in productionLineData)
                {
                    ProductionLineEfficiencyReport pe = DataConvert.Convert2ProductionLineEfficiency(item);
                    if (quota != null)
                        pe.EfficiencyQuota = quota[pe.ProductionLine];
                    retList.Add(pe);
                }
                // 3. 保存效率指标到DB中
                if (quota != null)
                    SaveEfficiencyQuota(year, quota, 1);
            }
            return retList;
        }
        private List<ProductionEfficiency> GetAllProductionLineOrderList(DateTime year)
        {
            try
            {
                List<ProductionEfficiency> retList = new List<ProductionEfficiency>();
                // 获得指定年内的所有生产线
                //List<string> productionLines = GetAllProductionLineName(year);
                List<string> productionLines = this.GetAllProductionLineNames();
                foreach (string proLine in productionLines)
                {
                    ProductionEfficiency pe = new ProductionEfficiency();
                    pe.ProductionLine = proLine;
                    pe.OutPutDic = new Dictionary<int, List<OrderProductionLineTime>>();
                    // 取得指定生产线此年的所有订单列表
                    for (int i = 0; i < 12; i++)
                    {
                        // 取得系统记录里面的订单列表
                        List<OrderProductionLineTime> sysOrderList = GetSYSOrderListByProLineMonth(proLine, year.AddMonths(i), year.AddMonths(i + 1));
                        List<OrderProductionLineTime> erpOrderList = GetERPOrderListByProLineMonth(proLine, year.AddMonths(i), year.AddMonths(i + 1));
                        List<OrderProductionLineTime> orderList = MergerOrderList(sysOrderList, erpOrderList);
                        pe.OutPutDic.Add(i, orderList);
                    }
                    retList.Add(pe);
                }
                return retList;
            }
            catch (Exception ex)
            {
                string msg = string.Format("SQLDataProcess.GetAllProductionLineOrderList() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                return null;
            }
        }
        private List<string> GetAllProductionLineName(DateTime year)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                List<string> retList = new List<string>();
                string query = string.Format(GET_ALLPRODUCTIONLINE_FROM_SYS, year, year.AddYears(1));
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        string proLine = reader["ProductionLine"].ToString();
                        if (!string.IsNullOrEmpty(proLine))
                            retList.Add(proLine);
                    }
                    reader.Close();
                }
                return retList;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetAllProductionLineName() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
                return null;
            }
        }
        private List<OrderProductionLineTime> GetSYSOrderListByProLineMonth(string proLine, DateTime startTime, DateTime endTime)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                List<OrderProductionLineTime> retList = new List<OrderProductionLineTime>();
                // 获得指定指定时间段内的所有系统记录下的JCJ订单
                string query = string.Format(GET_ALLJCJORDER_BY_PROLINE, proLine, startTime, endTime);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        DateTime st = DateTime.Parse(reader["StartTime"].ToString());
                        DateTime et = DateTime.Parse(reader["UpdateTime"].ToString());
                        st = st < startTime ? startTime : st;
                        et = et > endTime ? endTime : et;
                        // 如果开始和最后的记录时间小于5分钟则不记录
                        if ((et - st).TotalMinutes < 5)
                            continue;
                        else
                        {
                            string orderid = reader["OrderID"].ToString();
                            if (orderid == "MO0000000000")
                                continue;
                            OrderProductionLineTime obj = new OrderProductionLineTime();
                            obj.OrderID = orderid;
                            obj.ProductionLine = proLine;
                            obj.StartTime = st;
                            obj.EndTime = et;
                            retList.Add(obj);
                        }
                    }
                    reader.Close();
                }
                for (int i = 0; i < retList.Count; i++)
                {
                    retList[i].Weight = this.GetProLineWeight(retList[i].ProductionLine, retList[i].StartTime, retList[i].EndTime);
                    // 如果重量为0，那么当前的订单所在的生产线有可能没有装PLC，那么从ERP里面查找相应的产量
                    if (retList[i].Weight == 0)
                    {
                        retList[i].Weight = this.GetProLineWeightFromERP(retList[i].OrderID, retList[i].ProductionLine, retList[i].StartTime, retList[i].EndTime);
                    }
                    // 如果仍然为0，那么抛弃掉当前的订单
                    if (retList[i].Weight == 0)
                    {
                        retList.RemoveAt(i);
                        i--;
                    }
                }
                return retList;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetSYSOrderListByProLineMonth() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
                return null;
            }
        }
        private List<OrderProductionLineTime> GetERPOrderListByProLineMonth(string proLine, DateTime startTime, DateTime endTime)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                List<OrderProductionLineTime> retList = new List<OrderProductionLineTime>();
                string query = string.Format(GET_ORDERINFO_BY_PROLINE, proLine, startTime, endTime);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        OrderProductionLineTime obj = new OrderProductionLineTime();
                        obj.ProductionLine = proLine;
                        obj.OrderID = reader["OrderID"].ToString().Substring(2);
                        if (string.IsNullOrEmpty(reader["StartTime"].ToString()))
                            continue;
                        obj.StartTime = DateTime.Parse(reader["StartTime"].ToString());
                        obj.EndTime = DateTime.Parse(reader["EndTime"].ToString());
                        if (string.IsNullOrEmpty(reader["Count"].ToString()))
                            continue;
                        obj.Weight = double.Parse(reader["Count"].ToString());
                        retList.Add(obj);
                    }
                    reader.Close();
                }
                return retList;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetERPOrderListByProLineMonth() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
                return null;
            }
        }
        private List<OrderProductionLineTime> MergerOrderList(List<OrderProductionLineTime> sysList, List<OrderProductionLineTime> erpList)
        {
            List<OrderProductionLineTime> retList = new List<OrderProductionLineTime>();
            foreach (OrderProductionLineTime item in sysList)
            {
                retList.Add(item);
                for (int i = 0; i < erpList.Count; i++)
                {
                    // 如果系统里面已经记录了这个ID，那么从ERP里面删除掉相应重复的订单数据
                    if (item.OrderID == erpList[i].OrderID)
                    {
                        erpList.RemoveAt(i);
                        break;
                    }
                }
            }
            if (erpList.Count > 0)
            {
                foreach (OrderProductionLineTime item in erpList)
                { retList.Add(item); }
            }
            return retList;
        }
        private double GetProLineWeight(string proline, DateTime startTime, DateTime endTime)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                double weight = 0;
                double w1 = 0, w2 = 0, w3 = 0;
                string query = string.Format(GET_OUTPUT_BY_ORDERIDTIME, proline, startTime, endTime);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    bool isFirstLine = true;
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        double tmpw1 = double.Parse(reader["Bal1RealCount"].ToString());
                        double tmpw2 = double.Parse(reader["Bal2RealCount"].ToString());
                        double tmpw3 = double.Parse(reader["Bal3RealCount"].ToString());
                        if (isFirstLine && (tmpw1 > 0 || tmpw2 > 0 || tmpw3 > 0))
                        {
                            w1 = tmpw1;
                            w2 = tmpw2;
                            w3 = tmpw3;
                            weight = w1 + w2 + w3;
                            isFirstLine = false;
                        }
                        if (!isFirstLine)
                        {
                            if (w1 < tmpw1 || w2 < tmpw2 || w3 < tmpw3)
                                break;
                            else
                            {
                                w1 = tmpw1;
                                w2 = tmpw2;
                                w3 = tmpw3;
                                if (w1 == 0 && w2 == 0 && w3 == 0)
                                    break;
                            }
                        }
                    }
                    reader.Close();
                }
                weight -= (w1 + w2 + w3);
                return weight;
            }
            catch (SqlException ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetProLineWeight() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
                return 0;
            }
        }
        private void GetProLineWeight2(ref ProductionReportSummary summary)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                double weight = 0;
                double w1 = 0, w2 = 0, w3 = 0, sw1 = 0, sw2 = 0, sw3 = 0;
                string query = string.Format(GET_OUTPUT_BY_ORDERIDTIME, summary.ProductionLine, summary.StartTime, summary.UpdateTime);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    bool isFirstLine = true;
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        double tmpw1 = double.Parse(reader["Bal1RealCount"].ToString());
                        double tmpw2 = double.Parse(reader["Bal2RealCount"].ToString());
                        double tmpw3 = double.Parse(reader["Bal3RealCount"].ToString());
                        if (isFirstLine && (tmpw1 > 0 || tmpw2 > 0 || tmpw3 > 0))
                        {
                            w1 = tmpw1;
                            w2 = tmpw2;
                            w3 = tmpw3;
                            sw1 = tmpw1;
                            sw2 = tmpw2;
                            sw3 = tmpw3;
                            weight = w1 + w2 + w3;
                            isFirstLine = false;
                        }
                        if (!isFirstLine)
                        {
                            if (w1 < tmpw1 || w2 < tmpw2 || w3 < tmpw3)
                                break;
                            else
                            {
                                if (tmpw1 == 0 && tmpw2 == 0 && tmpw3 == 0)
                                    break;
                                else
                                {
                                    w1 = tmpw1;
                                    w2 = tmpw2;
                                    w3 = tmpw3;
                                }
                            }
                        }
                    }
                    reader.Close();
                }
                summary.Bal1TargetCount = sw1 - w1;
                summary.Bal2TargetCount = sw2 - w2;
                summary.Bal3TargetCount = sw3 - w3;
                weight -= (w1 + w2 + w3);
                if (weight != 0)
                {
                    summary.RateConsumption = summary.PowerConsum / weight;
                    TimeSpan span = (TimeSpan)(summary.UpdateTime - summary.StartTime);
                    double hours = span.TotalHours;
                    if (hours != 0)
                        summary.Efficiency = weight / hours;
                    // 计算预计结束时间
                    if (summary.Efficiency != 0 && summary.Count > 0)
                    {
                        double remainW = summary.Count - weight;
                        double remainH = remainW / summary.Efficiency;
                        summary.EstimateEndTime = ((DateTime)summary.UpdateTime).AddHours(remainH);
                    }
                }
            }
            catch (SqlException ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetProLineWeight2() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
            }
        }
        private double GetProLineWeightFromERP(string orderID, string proline, DateTime startTime, DateTime endTime)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                double retWeight = 0;
                if (!string.IsNullOrEmpty(proline))
                {
                    string query = string.Format(GET_ORDERINFO_FROM_ERP3, orderID, proline, startTime, endTime);
                    SqlConnection conn = new SqlConnection();
                    conn.ConnectionString = this.ConnString;
                    conn.Open();
                    using (cmd = new SqlCommand(query, conn))
                    {
                        cmd.CommandTimeout = 0;
                        reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                        if (reader.Read())
                        {
                            retWeight = double.Parse(reader["Count"].ToString());
                        }
                        reader.Close();
                    }
                }
                return retWeight;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetProLineWeightFromERP() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
                return 0;
            }
        }
        public void SaveEfficiencyQuota(DateTime year, Dictionary<string, int> quota, int effType)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                foreach (KeyValuePair<string, int> item in quota)
                {
                    bool isExist = false;
                    string query = string.Format(GET_EFFIQUOTA_BY_TYPE, effType, item.Key);
                    SqlConnection conn = new SqlConnection();
                    conn.ConnectionString = this.ConnString;
                    conn.Open();
                    using (cmd = new SqlCommand(query, conn))
                    {
                        cmd.CommandTimeout = 0;
                        reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                        if (reader.Read())
                        { isExist = true; }
                        reader.Close();
                    }
                    if (isExist)
                        query = string.Format(UPDATE_EFFIQUOTA, effType, item.Key, item.Value);
                    else
                        query = string.Format(INSERT_EFFIQUOTA, item.Key, effType, item.Value);
                    conn.Open();
                    using (cmd = new SqlCommand(query, conn))
                    {
                        cmd.CommandTimeout = 0;
                        cmd.ExecuteNonQuery(); 
                    }
                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.SaveEfficiencyQuota() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
            }
        }

        public Dictionary<string, int> GetEfficiencyQuota(int effType)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                Dictionary<string, int> quota = new Dictionary<string,int>();
                List<string> typeList = new List<string>();
                if (effType == 1)
                {
                    typeList = this.GetAllProductionLineNames();
                }
                else
                {
                    typeList = this.GetAllTeamNames();
                }
                foreach(string name in typeList)
                {
                    bool isExist = false;
                    string query = string.Format(GET_EFFIQUOTA_BY_TYPE, effType, name);
                    SqlConnection conn = new SqlConnection();
                    conn.ConnectionString = this.ConnString;
                    conn.Open();
                    using (cmd = new SqlCommand(query, conn))
                    {
                        cmd.CommandTimeout = 0;
                        reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                        if (reader.Read())
                        {
                            isExist = true;
                            int fq = int.Parse(reader["EffQuota"].ToString());
                            quota.Add(name, fq);
                        }
                        reader.Close();
                    }
                    if (!isExist)
                    {
                        query = string.Format(INSERT_EFFIQUOTA, name, effType, 0);
                        conn = new SqlConnection();
                        conn.ConnectionString = this.ConnString;
                        conn.Open();
                        using (cmd = new SqlCommand(query, conn))
                        {
                            cmd.CommandTimeout = 0;
                            cmd.ExecuteNonQuery();
                        }
                        conn.Close();
                        quota.Add(name, 0);
                    }
                }
                
                return quota;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetEfficiencyQuota() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
                return null;
            }
        }
        
        public List<ProductionLineStandby> GetProductionLineStandby2(DateTime year)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                ////ReConnectSQL();
                List<ProductionLineStandby> retList = new List<ProductionLineStandby>();
                string query = string.Format(GET_PROLINESTANDBY, year.Year);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        ProductionLineStandby obj = new ProductionLineStandby();
                        obj.ProductionLine = reader["ProductionLine"].ToString();
                        obj.JanTime = double.Parse(reader["JanTime"].ToString());
                        obj.FebTime = double.Parse(reader["FebTime"].ToString());
                        obj.MarTime = double.Parse(reader["MarTime"].ToString());
                        obj.AprTime = double.Parse(reader["AprTime"].ToString());
                        obj.MayTime = double.Parse(reader["MayTime"].ToString());
                        obj.JunTime = double.Parse(reader["JunTime"].ToString());
                        obj.JulTime = double.Parse(reader["JulTime"].ToString());
                        obj.AugTime = double.Parse(reader["AugTime"].ToString());
                        obj.SepTime = double.Parse(reader["SepTime"].ToString());
                        obj.OctTime = double.Parse(reader["OctTime"].ToString());
                        obj.NovTime = double.Parse(reader["NovTime"].ToString());
                        obj.DecTime = double.Parse(reader["DecTime"].ToString());
                        obj.MaxTime = double.Parse(reader["MaxTime"].ToString());
                        obj.MinTime = double.Parse(reader["MinTime"].ToString());
                        retList.Add(obj);
                    }
                    reader.Close();
                }
                retList.Sort((left, right) =>
                {
                    int proline1 = int.Parse(left.ProductionLine.Substring(1));
                    int proline2 = int.Parse(right.ProductionLine.Substring(1));
                    return proline2 - proline1;
                });
                return retList;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetProductionLineStandby2() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
                return null;
            }
        }


        public List<ProductionLineStandby> GetProductionLineStandby(DateTime year)
        {
            List<ProductionLineStandby> retList = new List<ProductionLineStandby>();
            // 1. 取出指定年内所有生产线
            List<string> prolines = this.GetAllProductionLineNames();
            // 2. 取出每条生产线的待机时间
            foreach (string proline in prolines)
            {
                ProductionLineStandby ps = this.GetProductionLineStandbyByYear(proline, year);
                retList.Add(ps);
            }
            return retList;
        }
        private ProductionLineStandby GetProductionLineStandbyByYear(string proline, DateTime year)
        {
            double maxhours = double.MinValue;
            double minhours = double.MaxValue;
            ProductionLineStandby obj = new ProductionLineStandby();
            obj.ProductionLine = proline;
            DateTime startTime = year;
            while (startTime < year.AddYears(1))
            {
                double hours = GetProductionLineStandbyByYear(proline, startTime, startTime.AddMonths(1).AddSeconds(-1));
                maxhours = maxhours > hours ? maxhours : hours;
                if (hours > 0.001)
                    minhours = minhours < hours ? minhours : hours;
                switch (startTime.Month)
                {
                    case 1:
                        obj.JanTime = hours;
                        break;
                    case 2:
                        obj.FebTime = hours;
                        break;
                    case 3:
                        obj.MarTime = hours;
                        break;
                    case 4:
                        obj.AprTime = hours;
                        break;
                    case 5:
                        obj.MayTime = hours;
                        break;
                    case 6:
                        obj.JunTime = hours;
                        break;
                    case 7:
                        obj.JulTime = hours;
                        break;
                    case 8:
                        obj.AugTime = hours;
                        break;
                    case 9:
                        obj.SepTime = hours;
                        break;
                    case 10:
                        obj.OctTime = hours;
                        break;
                    case 11:
                        obj.NovTime = hours;
                        break;
                    case 12:
                        obj.DecTime = hours;
                        break;
                }
                if (maxhours != double.MinValue)
                    obj.MaxTime = maxhours;
                if (minhours != double.MaxValue)
                    obj.MinTime = minhours;
                startTime = startTime.AddMonths(1);
            }
            return obj;
        }
        private double GetProductionLineStandbyByYear(string proline, DateTime startTime, DateTime endTime)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                double hours = 0;
                double sumMins = 0;
                string query = string.Format(GET_JCJCURRENT_BY_TIME, proline, startTime, endTime);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    // 由于数据是按照3分钟的间隔记录的，所以按照3分钟进行叠加
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        sumMins += IntervalMins;
                    }
                    reader.Close();
                }
                hours = sumMins / 60;
                return hours;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetProductionLineStandbyByYear() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
                return 0;
            }
        }

        private List<string> GetAllProductionLineNames()
        {
            string[] deviceList = PRET.DBAdapter.Properties.Resources.ProlineList.Split(new char[] { ',' });
            List<string> lists = deviceList.ToList();
            return lists;
        }
        private List<string> GetAllTeamNames()
        {
            string[] deviceList = PRET.DBAdapter.Properties.Resources.TeamList.Split(new char[] { ',' });
            List<string> lists = deviceList.ToList();
            return lists;
        }
        #endregion

        #region 获得班组效率数据
        public Dictionary<string, List<double>> GetTeamYearWeight2(DateTime year)
        {
            Dictionary<string, List<double>> retDic = new Dictionary<string, List<double>>();
            List<string> teamlist = GetAllTeamNames();
            foreach (string teamName in teamlist)
            {
                List<double> TeamYearWeight = GetTeamWeightInYear(teamName, year);
                retDic.Add(teamName, TeamYearWeight);
            }
            return retDic;
        }
        private List<double> GetTeamWeightInYear(string teamName, DateTime year)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                List<double> retList = new List<double>();
                string strType = "";
                if (teamName == "A班")
                    strType = "AWeight";
                else if (teamName == "B班")
                    strType = "BWeight";
                else
                    return retList;
                for(int i = 0; i < 12; i++)
                {
                    string month = string.Format("{0}/{1}/1", year.Year, i + 1);
                    DateTime startM = DateTime.Parse(month);
                    DateTime endM = startM.AddMonths(1);
                    string query = string.Format(GET_WEIGHT_BY_MONTH, startM, endM);
                    double output = 0;
                    SqlConnection conn = new SqlConnection();
                    conn.ConnectionString = this.ConnString;
                    conn.Open();
                    using (cmd = new SqlCommand(query, conn))
                    {
                        cmd.CommandTimeout = 0;
                        reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                        while(reader.Read())
                        {
                            if (reader[strType] != null)
                            {
                                double weight = 0;
                                if (double.TryParse(reader[strType].ToString(), out weight))
                                    output += weight;
                            }
                        }
                        reader.Close();
                    }
                    retList.Add(output);
                }                
                return retList;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetTeamWeightInYear() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
                return null;
            }
        }

        public Dictionary<string, List<double>> GetTeamYearWeight(DateTime year)
        {
            Dictionary<string, List<double>> retDic = new Dictionary<string, List<double>>();
            List<string> teamlist = GetAllTeamNames();
            foreach (string teamName in teamlist)
            {
                // 获得某班某年内的生产量
                List<double> TeamYearWeight = GetTeamMonthWeight(teamName, year);
                retDic.Add(teamName, TeamYearWeight);
            }
            return retDic;
        }
        private List<double> GetTeamMonthWeight(string teamName, DateTime year)
        {
            List<double> retList = new List<double>();
            DateTime startTime = year;
            while (startTime < year.AddYears(1))
            {
                double weight = GetTeamWeight(teamName, startTime, startTime.AddMonths(1));
                retList.Add(weight);
                startTime = startTime.AddMonths(1);
            }
            return retList;
        }
        private double GetTeamWeight(string teamName, DateTime startTime, DateTime endTime)
        {
            double weight = 0;
            // 1. 获得指定班组的工作时间列表
            List<ProductionTeamWorkTime> teamList = GetTeamList(teamName, startTime, endTime);
            if (teamList == null || teamList.Count == 0)
                return weight;
            // 2. 获得指定班组在指定生产线上的生产数量
            foreach (ProductionTeamWorkTime team in teamList)
            {
                weight += GetProductionTeamWeight(team);
            }
            return weight;
        }
        private List<ProductionTeamWorkTime> GetTeamList(string teamName, DateTime startTime, DateTime endTime)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                List<ProductionTeamWorkTime> retList = new List<ProductionTeamWorkTime>();
                string query = string.Format(GET_TEAMTIME, teamName, startTime, endTime);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        ProductionTeamWorkTime team = new ProductionTeamWorkTime();
                        team.ProductionLine = reader["ProductionLine"].ToString();
                        team.ProLineCode = reader["ProLineCode"].ToString();
                        team.TeamID = teamName;
                        team.StartTime = DateTime.Parse(reader["StartTime"].ToString());
                        team.StartTime = team.StartTime < startTime ? startTime : team.StartTime;
                        team.EndTime = DateTime.Parse(reader["EndTime"].ToString());
                        team.EndTime = team.EndTime > endTime ? endTime : team.EndTime;
                        retList.Add(team);
                    }
                    reader.Close();
                }
                return retList;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetTeamList() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
                return null;
            }
        }
        private double GetProductionTeamWeight(ProductionTeamWorkTime team)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                double weight = 0;
                string query = string.Format(GET_JCJDETAIL_BY_PRODUCTIONLINE2, team.ProductionLine, team.StartTime, team.EndTime);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    bool isStart = true;
                    float w1 = 0, w2 = 0, w3 = 0, wt = 0;
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        float tmpw1 = float.Parse(reader["Bal1RealCount"].ToString());
                        float tmpw2 = float.Parse(reader["Bal2RealCount"].ToString());
                        float tmpw3 = float.Parse(reader["Bal3RealCount"].ToString());
                        // 如果前几个数据都为0，继续读取下一个数据
                        if (isStart && tmpw1 == 0 && tmpw2 == 0 && tmpw3 == 0)
                            continue;
                        else if (isStart)
                        {
                            w1 = tmpw1;
                            w2 = tmpw2;
                            w3 = tmpw3;
                            wt = w1 + w2 + w3;
                            isStart = false;
                        }
                        else
                        {
                            if (w1 < tmpw1 || w2 < tmpw2 || w3 < tmpw3)
                            {
                                wt -= (w1 + w2 + w3);
                                weight += wt;
                                w1 = tmpw1;
                                w2 = tmpw2;
                                w3 = tmpw3;
                                wt = w1 + w2 + w3;
                            }
                            else if (tmpw1 == 0 && tmpw2 == 0 && tmpw3 == 0)
                            {
                                weight += wt;
                                isStart = true;
                            }
                            else
                            {
                                w1 = tmpw1;
                                w2 = tmpw2;
                                w3 = tmpw3;
                            }
                        }
                    }
                    wt -= (w1 + w2 + w3);
                    weight += wt;
                    reader.Close();
                }
                return weight;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetProductionTeamWeight() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
                return 0;
            }
        }

        public List<TeamEfficiencyReport> GetTeamEfficiencyReport(DateTime year)
        {
            List<TeamEfficiencyReport> retList = new List<TeamEfficiencyReport>();
            // 1. 取出指定年内所有班组的所有上工时间段内的产量
            List<TeamEfficiency> teamData = GetAllTeamOrderList(year);
            // 2. 从ERP提供的数据中取出每个班组的效率
            foreach (TeamEfficiency item in teamData)
            {
                TeamEfficiencyReport te = DataConvert.Convert2TeamEfficiency(item);
                retList.Add(te);
            }
            return retList;
        }
        private List<TeamEfficiency> GetAllTeamOrderList(DateTime year)
        {
            try
            {
                List<TeamEfficiency> retList = new List<TeamEfficiency>();
                // 获得指定年内的所有班组
                List<string> teams = GetAllTeamName(year);
                foreach (string teamID in teams)
                {
                    TeamEfficiency te = new TeamEfficiency();
                    te.TeamID = teamID;
                    // 取得指定班组此年的时间段的产量
                    te.OutPutList = GetOutputInTeamYear(teamID, year);
                    retList.Add(te);
                }
                return retList;
            }
            catch (Exception ex)
            {
                string msg = string.Format("SQLDataProcess.GetAllTeamOrderList() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                return null;
            }
        }
        private List<string> GetAllTeamName(DateTime year)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                List<string> retList = new List<string>();
                string query = string.Format(GET_ALLTEAM_FROM_SYS, year, year.AddYears(1));
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        string teamID = reader["TeamID"].ToString();
                        if (!string.IsNullOrEmpty(teamID))
                            retList.Add(teamID);
                    }
                    reader.Close();
                }
                return retList;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetAllTeamName() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
                return null;
            }
        }
        private List<TeamProductionLineTime> GetOutputInTeamYear(string teamID, DateTime year)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                // 1. 班组在生产线上的工作时间
                List<TeamProductionLineTime> retList = GetTeamProductionLineTimeList(teamID, year);
                if (retList == null || retList.Count == 0)
                    return retList;

                // 2. 查询指定生产线上均化机的实际产出
                string query = "";
                Dictionary<string, double> dicOrderUW = new Dictionary<string, double>();
                Dictionary<string, int> dicOrderNum = new Dictionary<string, int>();
                for (int i = 0; i < retList.Count; i++)
                {
                    // 这个生产线在指定时间内的订单产量
                    query = string.Format(GET_ORDERCOUNT_BY_ENDTIME2, retList[i].ProductionLine, retList[i].StartTime, retList[i].EndTime);
                    SqlConnection conn = new SqlConnection();
                    conn.ConnectionString = this.ConnString;
                    conn.Open();
                    using (cmd = new SqlCommand(query, conn))
                    {
                        cmd.CommandTimeout = 0;
                        reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                        while (reader.Read())
                        {
                            retList[i].Weight += float.Parse(reader["CurrCount"].ToString());
                            //string orderID = reader["OrderID"].ToString();
                            //int startNum = int.Parse(reader["StartNum"].ToString());
                            //int endNum = int.Parse(reader["EndNum"].ToString());
                            //if (dicOrderNum.Keys.Contains(orderID))
                            //    dicOrderNum[orderID] += endNum - startNum;
                            //else
                            //    dicOrderNum.Add(orderID, endNum - startNum);
                        }
                        reader.Close();
                    }
                    //foreach(KeyValuePair<string, int> item in dicOrderNum)
                    //{
                    //    double unitWeight = 0;
                    //    // 查询这个订单的单包重量
                    //    if (dicOrderUW.Keys.Contains(item.Key))
                    //        unitWeight = dicOrderUW[item.Key];
                    //    else
                    //    {
                    //        query = string.Format(GET_ORDERUNITWEIGHT_BY_ORDERID, item.Key);
                    //        using (cmd = new SqlCommand(query, conn))
                    //        {
                    //            reader = cmd.ExecuteReader();
                    //            if(reader.Read())
                    //            {
                    //                unitWeight = double.Parse(reader["UnitWeight"].ToString());
                    //                dicOrderUW.Add(item.Key, unitWeight);
                    //            }
                    //        }
                    //    }
                    //    // 如果单包重量不为0，那么累计在这条生产线上班组在指定时间段内的产量
                    //    if(unitWeight != 0)
                    //    {
                    //        retList[i].Weight += item.Value * unitWeight;
                    //    }
                    //}
                }
                return retList;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetOutputInTeamYear() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
                return null;
            }
        }
        //private double GetCurrentTeamEfficiency(string teamID)
        //{
        //    SqlCommand cmd = null;
        //    SqlDataReader reader = null;
        //    try
        //    {
        //        // 在产效率应该取最近一次生产完成的时间效率
        //        // 1. 取得最近一次完成的订单号，并获得挤出机开始时间和均化机结束时间
        //        OrderEfficiency latestOrder = null;
        //        string query = string.Format(GET_LATESTJHJORDER_BY_TEAM, teamID);
        //        using (cmd = new SqlCommand(query, conn))
        //        {
        //            reader = cmd.ExecuteReader();
        //            if (reader.Read())
        //            {
        //                latestOrder = new OrderEfficiency();
        //                latestOrder.OrderID = reader["OrderID"].ToString();
        //                DateTime? startTime = null;
        //                if (!string.IsNullOrEmpty(reader["StartTime"].ToString()))
        //                {
        //                    startTime = DateTime.Parse(reader["StartTime"].ToString());
        //                }
        //                DateTime? endTime = null;
        //                if (!string.IsNullOrEmpty(reader["EndTime"].ToString()))
        //                {
        //                    endTime = DateTime.Parse(reader["EndTime"].ToString());
        //                }
        //                if (startTime != null && endTime != null)
        //                {
        //                    TimeSpan span = (TimeSpan)((DateTime)endTime - (DateTime)startTime);
        //                    latestOrder.Mins = span.TotalMinutes;
        //                    latestOrder.EndTime = (DateTime)endTime;
        //                } 
        //            }
        //            reader.Close();
        //        }
        //        if (latestOrder == null)
        //            return 0;


        //        // 获得订单的挤出机耗时
        //        double mins = GetTimeCountFromJC(latestOrder.OrderID);
        //        latestOrder.Mins += mins;

        //        // 2. 根据订单号取得已经生成的生产量
        //        double weight = GetOrderWeightFromERP(latestOrder.OrderID);

        //        // 3. 算出当前效率
        //        double efficiency = (weight / latestOrder.Mins) * 60;
        //        return efficiency;
        //    }
        //    catch (Exception ex)
        //    {
        //        if (reader != null && !reader.IsClosed)
        //            reader.Close();
        //        //if (cmd != null)
        //            //cmd.Dispose();
        //        this.errorMessage = ex.Message;
        //        return 0;
        //    }
        //}
        public List<TeamProductionLineTime> GetTeamProductionLineTimeList(string teamID, DateTime year)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                List<TeamProductionLineTime> retList = new List<TeamProductionLineTime>();
                DateTime endYear = year.AddYears(1);
                string query = string.Format(GET_PRODUCTIONLINEWORKTIME_BY_TEAMYEAR, teamID, year, endYear);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        DateTime startTime = DateTime.Parse(reader["StartTime"].ToString());
                        DateTime endTime = DateTime.Parse(reader["EndTime"].ToString());
                        startTime = startTime < year ? year : startTime;
                        endTime = endTime > endYear ? endYear : endTime;
                        TimeSpan span = (TimeSpan)(endTime - startTime);
                        // 如果开始和结束时间小于3分钟则丢弃
                        if (span.TotalMinutes <= 3)
                            continue;
                        else
                        {
                            TeamProductionLineTime obj = new TeamProductionLineTime();
                            obj.TeamID = teamID;
                            obj.ProductionLine = reader["ProductionLine"].ToString();
                            obj.StartTime = startTime;
                            obj.EndTime = endTime;
                            retList.Add(obj);
                        }
                    }
                    reader.Close();
                }
                return retList;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetTeamProductionLineTimeList() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
                return null;
            }
        }

        public void SetTeamAttendanceHours(double AHours, double BHours, double CHours, double DHours, double EHours, double FHours, DateTime month)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            SqlCommand cmd = null;
            //SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                string strmonth = month.ToString("yyyy-MM");
                month = DateTime.Parse(strmonth);
                string query = string.Format(INSERT_TEAMATTENDANCEREST, month, DateTime.Now, AHours, BHours, CHours, DHours, EHours, FHours);
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    cmd.ExecuteNonQuery();
                }
                //// 1. 获得指定月份的班组休整时间
                //bool isExist = false;
                //string query = string.Format(GET_TEAMATTENDANCEREST_BY_MONTH, month);
                //using (cmd = new SqlCommand(query, conn))
                //{
                //    reader = cmd.ExecuteReader();
                //    if (reader.Read())
                //        isExist = true;
                //    reader.Close();
                //}

                //// 更新已有班组休整时间
                //if (isExist)
                //{
                //    query = string.Format(UPDATE_TEAMATTENDANCEREST_BY_MONTH, month, AHours, BHours, CHours, DHours, EHours, FHours);
                //    using (cmd = new SqlCommand(query, conn))
                //    { cmd.ExecuteNonQuery(); }
                //}
                //else
                //{
                //    query = string.Format(INSERT_TEAMATTENDANCEREST, month, DateTime.Now, AHours, BHours, CHours, DHours, EHours, FHours);
                //    using (cmd = new SqlCommand(query, conn))
                //    { cmd.ExecuteNonQuery(); }
                //}
            }
            catch (Exception ex)
            {
                //if (reader != null && !reader.IsClosed)
                //    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.SetTeamAttendanceHours() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
            }
            finally
            {
                conn.Close();
            }
        }
        public List<TeamAttendanceRestHours> GetTeamAttendanceHoursList(DateTime month)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                List<TeamAttendanceRestHours> retList = new List<TeamAttendanceRestHours>();
                string strmonth = month.ToString("yyyy-MM");
                month = DateTime.Parse(strmonth);
                // 1. 获得指定月份的班组休整时间
                string query = string.Format(GET_TEAMATTENDANCEREST_BY_MONTH, month);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        TeamAttendanceRestHours obj = new TeamAttendanceRestHours();
                        obj.ATeamHours = double.Parse(reader["AHours"].ToString());
                        obj.BTeamHours = double.Parse(reader["BHours"].ToString());
                        obj.CTeamHours = double.Parse(reader["CHours"].ToString());
                        obj.DTeamHours = double.Parse(reader["DHours"].ToString());
                        obj.ETeamHours = double.Parse(reader["EHours"].ToString());
                        obj.FTeamHours = double.Parse(reader["FHours"].ToString());
                        obj.UpdateTime = DateTime.Parse(reader["UpdateTime"].ToString());
                        retList.Add(obj);
                    }
                    reader.Close();
                }
                return retList;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetTeamAttendanceHoursList() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
                return null;
            }
        }

        public TeamAttendanceRestHours GetTeamAttendanceHours(DateTime month)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                TeamAttendanceRestHours obj = new TeamAttendanceRestHours();
                string strmonth = month.ToString("yyyy-MM");
                month = DateTime.Parse(strmonth);
                // 1. 获得指定月份的班组休整时间
                string query = string.Format(GET_TEAMATTENDANCEREST_BY_MONTH, month);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        obj.ATeamHours += double.Parse(reader["AHours"].ToString());
                        obj.BTeamHours += double.Parse(reader["BHours"].ToString());
                        obj.CTeamHours += double.Parse(reader["CHours"].ToString());
                        obj.DTeamHours += double.Parse(reader["DHours"].ToString());
                        obj.ETeamHours += double.Parse(reader["EHours"].ToString());
                        obj.FTeamHours += double.Parse(reader["FHours"].ToString());
                    }
                    reader.Close();
                }
                return obj;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetTeamAttendanceHours() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
                return null;
            }
        }

        public Dictionary<string, List<double>> GetTeamRestTime(DateTime year)
        {
            Dictionary<string, List<double>> retDic = new Dictionary<string, List<double>>();
            foreach (KeyValuePair<string, string> item in TeamsHoursDic)
            {
                DateTime startTime = year;
                List<double> teamMonthHours = new List<double>();
                while (startTime < year.AddYears(1))
                {
                    double hours = this.GetTeamMonthRestTime(item.Value, startTime);
                    teamMonthHours.Add(hours);
                    startTime = startTime.AddMonths(1);
                }
                retDic.Add(item.Key, teamMonthHours);
            }
            return retDic;
        }

        private double GetTeamMonthRestTime(string attName, DateTime month)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                ////ReConnectSQL();
                double retHours = 0;
                string strmonth = month.ToString("yyyy-MM") + "-01";
                // 1. 获得指定月份的班组休整时间
                string query = string.Format(GET_TEAMATTENDANCEREST_BY_MONTH, strmonth);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        retHours += double.Parse(reader[attName].ToString());
                    }
                    reader.Close();
                }
                return retHours;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetTeamMonthRestTime() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
                return 0;
            }
        }
        #endregion

        #region 获得电视显示报表
        //public List<ProductionLineEfficiencyReport> GetProductionLineEfficiencyReportForTV(DateTime year)
        //{
        //    List<ProductionLineEfficiencyReport> retList = new List<ProductionLineEfficiencyReport>();
        //    // 1. 取出指定年内所有生产线的订单号
        //    List<ProductionEfficiency> productionLineData = GetAllProductionLineOrderList(year);
        //    // 2. 从ERP提供的数据中取出每个生产线的效率
        //    foreach (ProductionEfficiency item in productionLineData)
        //    {
        //        ProductionLineEfficiencyReport pe = DataConvert.Convert2ProductionLineEfficiency(item);
        //        retList.Add(pe);
        //    }
        //    return retList;
        //}
        //private List<ProductionEfficiency> GetAllProductionLineOrderListForTV(DateTime year)
        //{
        //    try
        //    {
        //        List<ProductionEfficiency> retList = new List<ProductionEfficiency>();
        //        // 获得指定年内的所有生产线
        //        List<string> productionLines = this.GetAllProductionLineNames();
        //        foreach (string proLine in productionLines)
        //        {
        //            ProductionEfficiency pe = new ProductionEfficiency();
        //            pe.ProductionLine = proLine;
        //            pe.OutPutDic = new Dictionary<int, List<OrderProductionLineTime>>();
        //            // 取得指定生产线此年的所有订单列表
        //            for (int i = 0; i < 12; i++)
        //            {
        //                List<OrderProductionLineTime> orderList = GetOrderListByProLineYear(proLine, year.AddMonths(i), year.AddMonths(i + 1));
        //                pe.OutPutDic.Add(i, orderList);
        //            }
        //            retList.Add(pe);
        //        }
        //        return retList;
        //    }
        //    catch (Exception ex)
        //    {
        //        string msg = string.Format("SQLDataProcess.GetAllProductionLineOrderListForTV() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
        //        LogRecord.Error(msg);
        //        return null;
        //    }
        //}
        public TVControlInfo ShowMessage()
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                ////ReConnectSQL();
                TVControlInfo info = new TVControlInfo();
                string msg = "";
                int fontSize = 50;
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(GET_MESSAGE, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    if (reader.Read())
                    {
                        if (!string.IsNullOrEmpty(reader["msg1"].ToString()))
                            msg = reader["msg1"].ToString();
                        else if (!string.IsNullOrEmpty(reader["msg2"].ToString()))
                            msg = reader["msg2"].ToString();
                        else if (!string.IsNullOrEmpty(reader["msg3"].ToString()))
                            msg = reader["msg3"].ToString();
                        else if (!string.IsNullOrEmpty(reader["msg4"].ToString()))
                            msg = reader["msg4"].ToString();
                        else if (!string.IsNullOrEmpty(reader["msg5"].ToString()))
                            msg = reader["msg5"].ToString();
                        else if (!string.IsNullOrEmpty(reader["msg6"].ToString()))
                            msg = reader["msg6"].ToString();
                        else if (!string.IsNullOrEmpty(reader["msg7"].ToString()))
                            msg = reader["msg7"].ToString();
                        else if (!string.IsNullOrEmpty(reader["msg8"].ToString()))
                            msg = reader["msg8"].ToString();
                        else if (!string.IsNullOrEmpty(reader["FontSize"].ToString()))
                            fontSize = int.Parse(reader["FontSize"].ToString());
                    }
                    reader.Close();
                }
                if (msg.Length > 0)
                {
                    msg = msg.Replace("\r", "");
                    msg = msg.Replace("\n", "");
                }
                info.Message = msg;
                info.FontSize = fontSize;
                return info;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.ShowMessage() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
                return null;
            }
        }

        public ShowConfig GetShowConfig()
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                ////ReConnectSQL();
                ShowConfig config = null;
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(GET_SHOWCONFIG, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    if (reader.Read())
                    {
                        config = new ShowConfig();
                        config.APP_WORD = int.Parse(reader["APP_WORD"].ToString());
                        config.APP_CTRL1 = int.Parse(reader["APP_CTRL1"].ToString());
                        config.APP_CTRL2 = int.Parse(reader["APP_CTRL2"].ToString());
                        config.APP_CTRL3 = int.Parse(reader["APP_CTRL3"].ToString());
                    }
                    reader.Close();
                }
                return config;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetShowConfig() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
                return null;
            }
        }

        public TVControlInfo GetTVControlInfo()
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                TVControlInfo retObj = new TVControlInfo();
                string msg = "";
                int fontSize = 50;
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(GET_MESSAGE, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    if (reader.Read())
                    {
                        if (!string.IsNullOrEmpty(reader["msg1"].ToString()))
                            msg = reader["msg1"].ToString();
                        else if (!string.IsNullOrEmpty(reader["msg2"].ToString()))
                            msg = reader["msg2"].ToString();
                        else if (!string.IsNullOrEmpty(reader["msg3"].ToString()))
                            msg = reader["msg3"].ToString();
                        else if (!string.IsNullOrEmpty(reader["msg4"].ToString()))
                            msg = reader["msg4"].ToString();
                        else if (!string.IsNullOrEmpty(reader["msg5"].ToString()))
                            msg = reader["msg5"].ToString();
                        else if (!string.IsNullOrEmpty(reader["msg6"].ToString()))
                            msg = reader["msg6"].ToString();
                        else if (!string.IsNullOrEmpty(reader["msg7"].ToString()))
                            msg = reader["msg7"].ToString();
                        else if (!string.IsNullOrEmpty(reader["msg8"].ToString()))
                            msg = reader["msg8"].ToString();
                        else if (!string.IsNullOrEmpty(reader["FontSize"].ToString()))
                            fontSize = int.Parse(reader["FontSize"].ToString());
                    }
                    reader.Close();
                }

                conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(GET_SHOWCONFIG, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    if (reader.Read())
                    {
                        retObj.Time = int.Parse(reader["APP_WORD"].ToString());
                    }
                    reader.Close();
                }
                retObj.Message = msg;
                retObj.FontSize = fontSize;

                return retObj;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetTVControlInfo() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
                return null;
            }
        }

        public void SaveTVControlInfo(TVControlInfo info)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(DELETE_MESAGE, conn))
                {
                    cmd.CommandTimeout = 0;
                    cmd.ExecuteNonQuery();
                }
                conn.Close();
                int fontsize = info.FontSize;
                if (fontsize > 50 || fontsize < 1)
                    fontsize = 50;
                string strSql = string.Format(INSERT_MESSAGE, info.Message, fontsize);
                conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(strSql, conn))
                {
                    cmd.CommandTimeout = 0;
                    cmd.ExecuteNonQuery();
                }
                conn.Close();

                bool isUpdate = false;
                conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(GET_SHOWCONFIG, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    if (reader.Read())
                        isUpdate = true;
                    reader.Close();
                }

                if (isUpdate)
                    strSql = string.Format(UPDATE_SHOWCONFIG_WORD, info.Time);
                else
                    strSql = string.Format(INSERT_SHOWCONFIG, info.Time, 1, 0, 0);
                conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(strSql, conn))
                { 
                    cmd.CommandTimeout = 0;
                    cmd.ExecuteNonQuery(); 
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.SaveTVControlInfo() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
            }
        }
        #endregion

        #region 获得电视显示班组效率数据
        public List<TeamEfficiencyReport> GetTeamEfficiencyReportForTV(DateTime year)
        {
            List<TeamEfficiencyReport> retList = new List<TeamEfficiencyReport>();
            // 1. 取出指定年内所有班组的所有上工时间段内的产量
            List<TeamEfficiency> teamData = GetAllTeamOrderListForTV(year);
            // 2. 从ERP提供的数据中取出每个班组的效率
            foreach (TeamEfficiency item in teamData)
            {
                TeamEfficiencyReport te = DataConvert.Convert2TeamEfficiency(item);
                retList.Add(te);
            }
            return retList;
        }
        private List<TeamEfficiency> GetAllTeamOrderListForTV(DateTime year)
        {
            try
            {
                List<TeamEfficiency> retList = new List<TeamEfficiency>();
                // 获得指定年内的所有班组
                List<string> teams = new List<string> { "A班", "B班", "C班", "D班", "E班", "F班" };
                foreach (string teamID in teams)
                {
                    TeamEfficiency te = new TeamEfficiency();
                    te.TeamID = teamID;
                    // 取得指定班组此年的时间段的产量
                    te.OutPutList = GetOutputInTeamYear(teamID, year);
                    retList.Add(te);
                }
                return retList;
            }
            catch (Exception ex)
            {
                string msg = string.Format("SQLDataProcess.GetAllTeamOrderListForTV() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                return null;
            }
        }
        #endregion

        #region 获得滑石母粒机的数据
        public List<HSMLJData> GetHSMLJReport(DateTime startTime, DateTime endTime)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                List<HSMLJData> retList = new List<HSMLJData>();
                string query = string.Format(GET_HSMLJ_TIME, startTime, endTime);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        HSMLJData obj = new HSMLJData();
                        obj.StartTime = DateTime.Parse(reader["StartTime"].ToString());
                        obj.EndTime = DateTime.Parse(reader["EndTime"].ToString());
                        obj.WorkHours = ((TimeSpan)(obj.EndTime - obj.StartTime)).TotalMinutes;
                        obj.MainA = double.Parse(reader["MainA"].ToString());
                        obj.MainB = double.Parse(reader["MainB"].ToString());
                        obj.ExtraA = double.Parse(reader["ExtraA"].ToString());
                        obj.ExtraB = double.Parse(reader["ExtraB"].ToString());
                        double sp = double.Parse(reader["StartPower"].ToString());
                        double ep = double.Parse(reader["EndPower"].ToString());
                        obj.PowerCount = ep - sp;
                        double weight = obj.MainA + obj.MainB + obj.ExtraA + obj.ExtraB;
                        if (weight != 0)
                            obj.RateConsumption = obj.PowerCount / weight;
                        retList.Add(obj);
                    }
                    reader.Close();
                }
                retList.Sort((left, right) =>
                {
                    TimeSpan span = right.StartTime - left.StartTime;
                    return (int)span.TotalMinutes;
                });
                return retList;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("SQLDataProcess.GetHSMLJReport() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                ////ReConnectSQL();
                return null;
            }
        }
        #endregion
    }
}
